Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

If statements with Dates

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 

1 Solution

Accepted Solutions
manojkvrajan
Contributor III

Re: If statements with Dates

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.

  1. Create a table named Table_Temp that would join TORDFD.txt and TORDOP.txt data with the common field (as Key).
  2. Create a Table_Actual using the RESIDENT LOAD incorporating the IF logic
  3. 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;

4 Replies
manojkvrajan
Contributor III

Re: If statements with Dates

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,

Not applicable

Re: If statements with Dates

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!!!

manojkvrajan
Contributor III

Re: If statements with Dates

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.

  1. Create a table named Table_Temp that would join TORDFD.txt and TORDOP.txt data with the common field (as Key).
  2. Create a Table_Actual using the RESIDENT LOAD incorporating the IF logic
  3. 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;

Not applicable

Re: If statements with Dates

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!

Community Browser