topic Re: Create a table between two dates in QlikView

Create a table between two dates

thenextlevel — Thu, 06 Mar 2014 16:44:42 GMT

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

Fig2

OderNo	Date	Outstanding	Delivered
1111	01/04/2013	1
1111	02/04/2013	1
1111	03/04/2013	1
1111	04/04/2013		1
2222	03/05/2013	1
2222	04/05/2013	1
2222	05/05/2013		1

Re: Create a table between two dates

Thu, 06 Mar 2014 16:54:19 GMT

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

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

Vinay

Re: Create a table between two dates

Thu, 06 Mar 2014 17:02:12 GMT

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;

Re: Create a table between two dates

thenextlevel — Thu, 06 Mar 2014 17:05:42 GMT

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.

Re: Create a table between two dates

thenextlevel — Thu, 06 Mar 2014 17:12:24 GMT

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

Re: Create a table between two dates

MK_QSL — Thu, 06 Mar 2014 17:44:08 GMT

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;

Re: Create a table between two dates

Thu, 06 Mar 2014 18:05:05 GMT

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

Re: Create a table between two dates

thenextlevel — Fri, 07 Mar 2014 15:09:02 GMT

Byron

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

Greatly appreciated!!