topic Re: If statements with Dates in QlikView

If statements with Dates

Wed, 15 Aug 2012 16:51:28 GMT

I would like to create an expression.

(not sure whether to place in the Script or in a Chart)

But I have 2 fields Titled

[Planned Date]-- list of dates

[Actual Date]-- list of dates

Also I created a reference field:

STATUS DAYS_STATUS

Early +1

Late -1

On Time 0

Scheduled 0

If the Planned Date is before the Actual Date, The Status should be 'On Time' with a DAYS_STATUS of 0.

If the Actual Date is before the Planned Date, The Status is Early with DAYS_STATUS of 1.

If the Planned Date is before Today and the Actual Date is blank/ null, the Stutus should be 'Late' with a DAYS_STATUS of -1

If The Planned Date is later than Today's date, the Status should be Scheduled with a DAYS_STATUS of 0.

So far.. I've come up with this type of script.. but it's not working and I'm not sure what to do..

If [date([Planned Date]) < date([Actual Date]), then STATUS= 'On Time' and DAYS_STATUS = 0.

Ideally, What I'd like to create is a table which shows the Order Number, the STATUS to see which parts are on time, early, late & still scheduled

and to make a count of the DAYS_STATUS to see how behind production is.

Any Suggestions would be very appreciated!

--Please Write the Script in the Comment instead of attaching files

Re: If statements with Dates

manojkvrajan — Wed, 15 Aug 2012 17:02:36 GMT

Please add these two statements to your Load script to generate the table fields STATUS and DAYS_STATUS. I hope it meets your requirements.

IF ([Planned Date] < [Actual Date], 'On Time',

IF ([Planned Date] > [Actual Date], 'Early',

IF ([Planned Date] < today() AND isnull([Actual Date]) = -1, 'Late',

IF ([Planned Date] > today(), 'Scheduled')))) AS STATUS,

IF ([Planned Date] < [Actual Date], 0,

IF ([Planned Date] > [Actual Date], 1,

IF ([Planned Date] < today() AND isnull([Actual Date]) = -1, -1,

IF ([Planned Date] > today(), 0)))) AS DAYS_STATUS,

Re: If statements with Dates

Wed, 15 Aug 2012 17:28:58 GMT

The only issue I'm having is that

Planned date is pulling from TORDFD.txt, while Actual Date is pulling form TORDOP.txt..

So I've tried putting the statement in the 2nd load statement of the Actual Date,

But when I tried running it, it had an error saying that it couldn't recognize the Planned Date..

Any suggestions on where I should load the data?

Thank you!!!

Re: If statements with Dates

manojkvrajan — Wed, 15 Aug 2012 17:40:07 GMT

There are lot of work arounds to it. However, I would begin with the following logic. Please let me know if it helps or upload a sample datasource file and let me look at it.

Create a table named Table_Temp that would join TORDFD.txt and TORDOP.txt data with the common field (as Key).
Create a Table_Actual using the RESIDENT LOAD incorporating the IF logic
Drop the Table_Temp

Sample script:

Table_Temp:

Load Key,

FROM TORDFD.txt;

JOIN

Load Key,

FROM TORDOP.txt;

Table_Actual:

LOAD *,

IF ([Planned Date] < [Actual Date], 'On Time',

IF ([Planned Date] > [Actual Date], 'Early',

IF ([Planned Date] < today() AND isnull([Actual Date]) = -1, 'Late',

IF ([Planned Date] > today(), 'Scheduled')))) AS STATUS,

IF ([Planned Date] < [Actual Date], 0,

IF ([Planned Date] > [Actual Date], 1,

IF ([Planned Date] < today() AND isnull([Actual Date]) = -1, -1,

IF ([Planned Date] > today(), 0)))) AS DAYS_STATUS

RESIDENT Table_Temp;

DROP TABLE Table_Temp;

Re: If statements with Dates

Wed, 15 Aug 2012 18:38:06 GMT

IT WORKED!!!!!!

There was just one error that said:

Unknown statement

Table_Temp:

But, the data uploaded!

& Everything else seems to be functioning just fine

Thank you so much for your help, Manoj!