Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
dnt503
Contributor II
Contributor II

IF Statements Help

I'm currently working on the data load using IF statements below but keep getting an error for line 3 & 4. Can someone help point out what I'm doing wrong? I've included the sample data attached for reference of what I want as an outcome. Thank you in advance.

1: date(CREATED_DATE, 'MM/DD/YYYY') as [Date Opened],

2: STATUS_C as [Status],

3: if([STATUS_C]= 'Closed', 'Closed','Open') as [Status Flag],

4: If ([STATUS_C] ='Open',today()-Created Date as [Days Aged from Open Date],

5: If ([STATUS_C] ='Open',If( [Days Aged] >= 0 and [Days Aged] < 90, replace( class([Days Aged],30),'<= x <','-') & ' Days', If( [Days Aged] >= 90, '90 Days+'))) as [Open Timeframe],

kimdiep503_0-1711473433831.png

 

Labels (4)
1 Solution

Accepted Solutions
pravinboniface
Creator III
Creator III

@dnt503 1.  There are syntax errors 2.  Since [Days Aged from Open Date] is created in the same step, you cannot refer to it in this step.  I have changed the code to look as follows:

Note that unless CREATED_DATE is in the expected date format, the today()-CREATED_DATE will not work.   You may need to cast as needed.

If ([STATUS_C] ='Open',If ((today()-CREATED_DATE)<= 30, '0-30 days',
                    if ((today()-CREATED_DATE)<=60, '30-60 days',
                    if ((today()-CREATED_DATE)<=90, '60-90 days',
                    if ((today()-CREATED_DATE)<=91, '90+ days'))))) as [Open Timeframe] 

View solution in original post

3 Replies
pravinboniface
Creator III
Creator III

@dnt503 I see issues with line 4. Based on line 1, should the field be CREATED_DATE?  Also, there is a missing ) after that line.  Line 3 looks OK to me.

 If ([STATUS_C] ='Open',today()-Created Date as [Days Aged from Open Date],

Correct line:

 If ([STATUS_C] ='Open',today()-CREATED_DATE) as [Days Aged from Open Date],

dnt503
Contributor II
Contributor II
Author

Thank you! Line 4 works now. I'm still having issues with line 5.

 

line 4: If ([STATUS_C] ='Open',today()-CREATED_DATE) as [Days Aged from Open Date],


line 5: If ([STATUS_C] ='Open',If[Days Aged from Open Date]<= 30, '0-30 days',
                    if [Days Aged from Open Date]<=60,'30-60 days',
                    if [Days Aged from Open Date]<=90, '60-90 days,
                    if [Days Aged from Open Date]<=91, '90+ days' as [Open Timeframe],

pravinboniface
Creator III
Creator III

@dnt503 1.  There are syntax errors 2.  Since [Days Aged from Open Date] is created in the same step, you cannot refer to it in this step.  I have changed the code to look as follows:

Note that unless CREATED_DATE is in the expected date format, the today()-CREATED_DATE will not work.   You may need to cast as needed.

If ([STATUS_C] ='Open',If ((today()-CREATED_DATE)<= 30, '0-30 days',
                    if ((today()-CREATED_DATE)<=60, '30-60 days',
                    if ((today()-CREATED_DATE)<=90, '60-90 days',
                    if ((today()-CREATED_DATE)<=91, '90+ days'))))) as [Open Timeframe]