topic Re: Expand Date Range with Values in QlikView

Expand Date Range with Values

malmsteen1977 — Wed, 15 Nov 2017 14:35:38 GMT

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

Re: Expand Date Range with Values

malmsteen1977 — Thu, 23 Nov 2017 08:49:41 GMT

Anyone?

Re: Expand Date Range with Values

marcus_sommer — Thu, 23 Nov 2017 10:16:12 GMT

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

Re: Expand Date Range with Values

Anonymous — Thu, 23 Nov 2017 11:06:59 GMT

Hi.

Try This.

Re: Expand Date Range with Values

Anonymous — Fri, 24 Nov 2017 16:00:54 GMT

Here it is what you need! let me know

Re: Expand Date Range with Values

malmsteen1977 — Fri, 24 Nov 2017 16:27:25 GMT

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 🙂

Re: Expand Date Range with Values

antoniotiman — Fri, 24 Nov 2017 17:37:54 GMT

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;