Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

thenextlevel
New 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
Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Create a table between two dates

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;

7 Replies
Not applicable

Re: Create a table between two dates

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

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

Vinay


Not applicable

Re: Create a table between two dates

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
New Contributor II

Re: Create a table between two dates

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
New Contributor II

Re: Create a table between two dates

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

Re: Create a table between two dates

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

Re: Create a table between two dates

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
New Contributor II

Re: Create a table between two dates

Byron

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

Greatly appreciated!!

R

Community Browser