topic Re: Apply relevant data after an effective date in QlikView

Apply relevant data after an effective date

Thu, 06 Feb 2014 13:14:45 GMT

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

Re: Apply relevant data after an effective date

Gysbert_Wassenaar — Thu, 06 Feb 2014 14:02:11 GMT

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

Re: Apply relevant data after an effective date

Thu, 06 Feb 2014 14:10:20 GMT

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

Re: Apply relevant data after an effective date

Gysbert_Wassenaar — Thu, 06 Feb 2014 14:21:55 GMT

That sounds like you want/need the intervalmatch function. See this blog post: IntervalMatch