Skip to Content

Dateadd and Datediff equivalent in Universe IDT

Hey experts

I am trying to create an indicator field in my Universe where based on a certain criteria for my date in the table, the indicator has some values.

My indicator should say 'Y' if my date is in the last 12 months, 'P' if between 13 and 24 months and N otherwise.

Here is the screenshot of the sql formula I am using which works fine...

I have attached the screenshot because I cannot copy and paste in SCN forum ... don't know why!!

I have tried with 'timestampadd' function but cant seem to find the first day and last day of month equivalent ...

Here

any ideas?

cheers

Capture13.PNG (9.8 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Jul 12, 2016 at 06:56 PM

    That's right. My universe was a multi source and it does not handle sql functions very well. I have now switched it back to the single source and its all good now.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 07, 2016 at 01:55 PM

    Hi Buddy,

    Please create an Object in the Universe, copy and paste the case statement you've in the image (starting case......to..... end) in the Select clause of the object and try to parse it. That should work fine as long as the case statement you've is correct, the date column name is call_date, table name is table123 (or whatever), and you have the table123 added in the data foundation layer.

    You might want to update the case statement as

    case

    when call_date between ___ and ___ then 'last 12 months'

    when call_date between ___ and ___ then 'last 12 to 23 months'

    .

    .

    .

    else 'older than x months'

    end


    Thanks,

    Mahboob Mohammed

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 12, 2016 at 08:53 AM

    IDT is the semantic layer which maps the business language to technical language. As long as you use Single Source Connection, you can use every (almost) expression that are valid in the underlying relational database.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.