Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
In my qvd I have various fields such as Date and Unit so what I want to do is to apply the below costs (from a spreadsheet) based on the date in the qvd, e.g. For Unit A on 08/08/2013 the Cost is 10 but for Unit A on 23/11/2013
My spreadsheet looks like the below:
Unit | Start Date | End Date | Cost |
---|---|---|---|
A | 20/02/2013 | 09/10/2013 | 10 |
B | 19/05/2013 | 20/09/2013 | 11 |
C | 20/04/2013 | 25/04/2013 | 12 |
A | 10/10/2013 | 23 | |
B | 01/10/2013 | 25 | |
C | 26/04/2013 | 26 |
Also when there is no end date the cost is active, I.e should be applied to the current data
Thanks,
Ralph
Create an extra field in the script to mark a Cost as active or inactive. Then use that field in your Qlikview document to select the active Costs.
LOAD
Unit,
[Start Date],
[End Date],
If(len(trim([End Date]))=0,1,0) as IsActive,
Cost
FROM ...myspreadsheet...;
What I need to do is to link the costs at a particular date to the data. i.e. if I select Unit A and then a date between 20/02/13 and 09/10/13 I want the quantity in the qvd to be multiplied by 10, whilst if I select a date greater than 10/10/13 I want the quantity to be multiplied by 23. So basically I need the cost to be appended to each date in the qvd
Thanks,
Ralph
That sounds like you want/need the intervalmatch function. See this blog post: IntervalMatch