Expand Date Range with Values

[malmsteen1977]

Hi Qlikers,

I have a similar problem as discussed in How to expand dates with values. | Qlik Community, but slightly different. I also have tried to adjust the program code to my needs, but with no success.

What I have as base is the Excel file attached. I have Customer, Parts, a Valid_Date and the Price (Rows A-D)

What I want to achieve is shown in Row F (desired Result). I want to expand the Parts per Customer with the Price that lays between the two dates on a daily basis.

I have marked the "transition" per Customer/Part/Date regarding the two prices.

Maybe anyone can help me.

Best,

Hannes

[malmsteen1977]

Anyone?

[marcus_sommer]

The logic isn't quite clear for me by the switch from one part-number to the next different part-number. Why running until the 17.01.2018?

- Marcus

[Anonymous]

Hi.

Try This.

[Anonymous]

Here it is what you need! let me know

[malmsteen1977]

Hi Marcus,

it's just an example of a continous date. The Date-Column is a kind of "From-Date", from where the price is valid. So the first Part (1234) can run til the end of time 🙂

[antoniotiman]

May be this

Temp:
LOAD * Inline [
Customer,Part,Date,Price
4711,1234,01/12/2017,1.54
4711,1234,05/01/2018,1.64
4711,2345,01/11/2017,1.87
4711,2345,24/02/2018,2.54
4712,3456,02/12/2017,6.45
4712,3456,05/03/2018,6.72
4712,4567,01/10/2017,10.56
4712,4567,01/11/2017,11.11
];
Temp1:
NoConcatenate
LOAD *,If(Customer=Peek(Customer) and Part=Peek(Part),Peek(Date)-1,Date) as Date1
Resident Temp
Order By Customer,Part,Date desc;
NoConcatenate
LOAD Customer,Part,Date(Date+IterNo()-1) as Date,Price
Resident Temp1
While Date+IterNo()-1 <= Date1;
Drop Tables Temp,Temp1;