Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thenextlevel
Contributor II
Contributor II

Create a table between two dates

Hello

I have a table that contains two dates (Fig1) and i would like to calculate the days inbetween and display them like Fig2.

What is the best way to create this table? I have created a 'For Next' to loop through the records in the script, but for thousands of orders this is very slow. Can i just generate the table without creating it in load script?

Any help would be greatly received.

Thankyou

Fig1

OrderNo.OrderDateDeliveryDate
111101/04/201304/04/2013
222203/05/201305/05/2013
333304/05/201328/02/2014
444412/12/201304/03/2014

Fig2

OderNoDateOutstandingDelivered
111101/04/20131
111102/04/20131
111103/04/20131
111104/04/20131
222203/05/20131
222204/05/20131
222205/05/20131
1 Solution

Accepted Solutions
Not applicable

Load this into your script

Data:

Load * inline [

OrderNo, OrderDate, DeliveryDate

1111, 01/04/2013, 04/04/2013

2222, 03/05/2013, 05/05/2013

3333, 04/05/2013, 28/02/2014

4444, 12/12/2013, 04/03/2014];

DataFinal:

Load

  OrderNo,

  Date,

  if(Date<DeliveryDate,1,null()) as Outstanding,

  if(Date=DeliveryDate,1,null()) as Delivered;

Load

  OrderNo,

  OrderDate,

  DeliveryDate,

  Date(OrderDate+IterNo()-1) as Date

Resident Data

While OrderDate+IterNo()-1 <= DeliveryDate;

Drop Table Data;

View solution in original post

7 Replies
Not applicable

why do you want to change? what is your objective?

Do you want to count how many days between two dates?

Vinay


Not applicable

Load this into your script

Data:

Load * inline [

OrderNo, OrderDate, DeliveryDate

1111, 01/04/2013, 04/04/2013

2222, 03/05/2013, 05/05/2013

3333, 04/05/2013, 28/02/2014

4444, 12/12/2013, 04/03/2014];

DataFinal:

Load

  OrderNo,

  Date,

  if(Date<DeliveryDate,1,null()) as Outstanding,

  if(Date=DeliveryDate,1,null()) as Delivered;

Load

  OrderNo,

  OrderDate,

  DeliveryDate,

  Date(OrderDate+IterNo()-1) as Date

Resident Data

While OrderDate+IterNo()-1 <= DeliveryDate;

Drop Table Data;

thenextlevel
Contributor II
Contributor II
Author

I want to be able to sum my outstanding orders and my delievered orders by day, week, month, year etc and be able to drill into those orders.

thenextlevel
Contributor II
Contributor II
Author

Hi Byron,

Wow that seems to work great, but i am struggling to understand how it works...where did the 'Date' field come from??

Many thanks

R

MK_QSL
MVP
MVP

Load

  OrderNo,

  OrderDate,

  DeliveryDate,

  Date(OrderDate+IterNo()-1) as Date

Resident Data

While OrderDate+IterNo()-1 <= DeliveryDate;

Here the table is giving you dates for OrderDate upto DeliveryDate by iteration.

Now this Date is being used in Preceding Load to find out Outstanding and Delivered.

In other ways the above script can be used as below, but Preceding Load is much faster ...

Data:

Load * inline [

OrderNo, OrderDate, DeliveryDate

1111, 01/04/2013, 04/04/2013

2222, 03/05/2013, 05/05/2013

3333, 04/05/2013, 28/02/2014

4444, 12/12/2013, 04/03/2014];

DataFinal:

Load

  OrderNo,

  OrderDate,

  DeliveryDate,

  Date(OrderDate+IterNo()-1) as Date

Resident Data

While OrderDate+IterNo()-1 <= DeliveryDate;

FINAL:

Load

  OrderNo,

  Date,

  if(Date<DeliveryDate,1,null()) as Outstanding,

  if(Date=DeliveryDate,1,null()) as Delivered

Resident DataFinal;

Drop Table Data, DataFinal;

Not applicable

Glad it did mate!

The date field i created in the first load

Load

  OrderNo,

  OrderDate,

  DeliveryDate,

  Date(OrderDate+IterNo()-1) as Date

Resident Data

While OrderDate+IterNo()-1 <= DeliveryDate;

it creates a date for each date that appears between the conditional where clause. The -1 after iterno() is because iterno() starts with the value 1, and we want the first date not the next

While OrderDate+IterNo()-1 <= DeliveryDate;


This is where it does a loop like check and creates our new table


DataFinal:

Load

  OrderNo,

  Date,

  if(Date<DeliveryDate,1,null()) as Outstanding,

  if(Date=DeliveryDate,1,null()) as Delivered;

Load

  OrderNo,

  OrderDate,

  DeliveryDate,

  Date(OrderDate+IterNo()-1) as Date

Resident Data

While OrderDate+IterNo()-1 <= DeliveryDate;

The preceding load is where we get the final result you want. Hope that clears it up for you. Let me know

Cheers,

Byron



thenextlevel
Contributor II
Contributor II
Author

Byron

Thanks so much for your explanation and the time you have taken to reply.

Greatly appreciated!!

R