topic Creating all dates in a date range in QlikView

Creating all dates in a date range

Tue, 07 Oct 2014 13:02:14 GMT

I want to be able to create a table with all of the possible dates based on just two dates provided in a table which I load.

I have an Excel spreadsheet which has a From Date and To Date along with some Customer IDs and Sales Values. A customer ID will have a specific date range and a Sales value.

So an example of the structure is:

From Date	To Date	Customer ID	Customer Name	Sales Value
01/10/2014	04/10/2014	0001	Joe Bloggs	21,000
01/10/2014	31/10/2014	0002	John Smith	23,000
01/10/2014	31/10/2014	0005	Stan Smith	47,000

I want to be able to take this structure in as it is and transform it so that I have every possible date for each From and To date for each customer.

I want to be able to transform the above to the following desired structure in the QlikView script:

Date	Customer	Customer ID	Sales Amount
01/10/2014	Joe Bloggs	0001	21,000
02/10/2014	Joe Bloggs	0001	21,000
03/10/2014	Joe Bloggs	0001	21,000
04/10/2014	Joe Bloggs	0001	21,000

The aim is then to create an expression to get the mode sales value based on each customer. So for the above example, the Sales amount for the above customer is 21,000.

Is there a way to do this?

Thanks

Re: Creating all dates in a date range

Gysbert_Wassenaar — Tue, 07 Oct 2014 13:27:40 GMT

See attached qvw.

Re: Creating all dates in a date range

mjayachandran — Tue, 07 Oct 2014 13:49:29 GMT

Sure we can do it see below:

Table:

Load * Inline [

From Date, To Date, Customer ID, Customer Name, Sales Value

01/10/2014, 04/10/2014, 0001, Joe Bloggs , 21,000

01/10/2014, 15/10/2014, 0002, John Smith, 23,000

];

RESULT:

LOAD [Customer ID], [Customer Name],[Sales Value],

Date(Date#([From Date],'dd/mm/yyyy')+iterno()-1) as Date

resident Table while Date#([From Date],'dd/mm/yyyy')+IterNo()-1 <=Date#([To Date],'dd/mm/yyyy');

drop table Table;

Re: Creating all dates in a date range

Daveed — Wed, 01 Sep 2021 16:57:51 GMT

Can this only be done with Loading InLine data? Is it incompatible with Loading from an Excel file?