Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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
See attached qvw.
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;
Can this only be done with Loading InLine data? Is it incompatible with Loading from an Excel file?