Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Apply relevant data after an effective date

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:

UnitStart DateEnd DateCost
A20/02/201309/10/201310
B19/05/201320/09/201311
C20/04/201325/04/201312
A10/10/201323
B01/10/201325
C26/04/201326


Also when there is no end date the cost is active, I.e should be applied to the current data

Thanks,

Ralph

Tags (2)
3 Replies

Re: Apply relevant data after an effective date

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


talk is cheap, supply exceeds demand
Not applicable

Re: Apply relevant data after an effective date

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

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


talk is cheap, supply exceeds demand
Community Browser