Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
malmsteen1977
Partner
Partner

Expand Date Range with Values

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

6 Replies
malmsteen1977
Partner
Partner
Author

Anyone?

marcus_sommer
MVP & Luminary
MVP & Luminary

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

matheus_silva
Creator
Creator

Hi.

Try This.

leledifra_pwc
Contributor
Contributor

Here it is what you need! let me know

malmsteen1977
Partner
Partner
Author

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
Master III
Master III

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;