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: 
Not applicable

Creating all dates in a date range

Hi

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 DateTo DateCustomer IDCustomer NameSales Value
01/10/201404/10/20140001Joe Bloggs

21,000

01/10/201431/10/20140002John Smith23,000
01/10/201431/10/20140005Stan Smith47,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:

DateCustomerCustomer IDSales Amount
01/10/2014Joe Bloggs000121,000
02/10/2014Joe Bloggs000121,000
03/10/2014Joe Bloggs000121,000
04/10/2014Joe Bloggs000121,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

3 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
mjayachandran
Creator II
Creator II

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;

Daveed
Contributor
Contributor

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