IF Statements Help

[dnt503]

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],

[pravinboniface]

@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]

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 · Accepted solution]

@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]