topic Re: Help with adding in a price change file and using the new prices in QlikView

Help with adding in a price change file and using the new prices

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a requirement that i have no idea how to implement.

I have some forecast data which is by Customer, Item and Period. I then have left joined the standard item prices to the forecast data.

This then allows me to use the forecast volume * the standard price to get a forecast value.

What i need is it to take into account a price change file, so for example..

for Item 01003 and customer 123 i would have the following forecast values

Period	Forecast Value
1	50
2	201
3	150
4	150
5	150
6	130
7	130
8	130
9	100
10	100
11	100
12	400
13	250

The standard price for this Item and this customer is 7.35

but in period 6 there will be a price change, the new price will be 7.10.

so what i need is to use the 7.35 for period 1-5 then 7.10 there after.

Period	Forecast Qty	Price	Forecast Value
1	50	7.35	367.5
2	201	7.35	1477.35
3	150	7.35	1102.5
4	150	7.35	1102.5
5	150	7.35	1102.5
6	130	7.1	923
7	130	7.1	923
8	130	7.1	923
9	100	7.1	710
10	100	7.1	710
11	100	7.1	710
12	400	7.1	2840
13	250	7.1	1775

I would appreciate any help given

Re: Help with adding in a price change file and using the new prices

sunny_talwar — Thu, 24 May 2018 16:16:00 GMT

Looks like a great candidate for Interval Match.... have you tried that?

Re: Help with adding in a price change file and using the new prices

hopkinsc — Thu, 24 May 2018 16:54:48 GMT

Hi Sunny,

I haven't yet, I am unsure how to get the calculations to take the correct price based on the period..

Re: Help with adding in a price change file and using the new prices

sunny_talwar — Thu, 24 May 2018 16:57:08 GMT

Is period linked to date? How do you know that P6 is when change happen. Because change is based on date 5/24/2018

Re: Help with adding in a price change file and using the new prices

hopkinsc — Thu, 24 May 2018 16:59:08 GMT

Yes, it is linked to date. so if you were to select the 24th May, this will filter to P6

Re: Help with adding in a price change file and using the new prices

hopkinsc — Fri, 25 May 2018 09:41:24 GMT

Morning Sunny,

did you have any thoughts on this?

Re: Help with adding in a price change file and using the new prices

hopkinsc — Wed, 30 May 2018 09:15:03 GMT

morning Sunny,

I don't suppose you've had a chance to think about how i can do this have you?

Re: Help with adding in a price change file and using the new prices

sunny_talwar — Wed, 30 May 2018 11:03:24 GMT

Today will be the day... will def. get back to you sometime today.

Re: Help with adding in a price change file and using the new prices

hopkinsc — Mon, 11 Jun 2018 08:32:01 GMT

Hi Sunny, I don't suppose you have had any luck with this?

Re: Help with adding in a price change file and using the new prices

hopkinsc — Mon, 25 Jun 2018 15:16:04 GMT

Can anyone help with this?

Re: Help with adding in a price change file and using the new prices

sunny_talwar — Mon, 25 Jun 2018 16:27:58 GMT

Finally, I made myself motivated to do this... check it out

Re: Help with adding in a price change file and using the new prices

sunny_talwar — Mon, 25 Jun 2018 16:45:43 GMT

I noticed that Period 6 repeared for Item = 01003 and Customer = 123... this is because the change happened in between the month.... so for the first 23 days the Price was 7.35 and then it changed to 7.1. But since the table box showed only Period, it showed both prices associated with Period 6. Is there a logic as to when the rate change actually takes place or is the above output the one you were looking for?

Re: Help with adding in a price change file and using the new prices

hopkinsc — Mon, 25 Jun 2018 16:49:59 GMT

ooh this looks promising!

i will test it properly tomorrow.

Thanks very much Sunny

Re: Help with adding in a price change file and using the new prices

hopkinsc — Mon, 25 Jun 2018 16:54:45 GMT

I am unsure on what sort of date field they want to use as yet, I have been waiting for a sample file from them for the last month!

I used the standard date but to be honest i think it will be linked to the period number instead, so i should only get 1 price if its linked on period

Will anything need to be changed if i change the link to period?

i haven't downloaded the QVW yet, i will do in the morning.

Re: Help with adding in a price change file and using the new prices

sunny_talwar — Mon, 25 Jun 2018 17:00:30 GMT

Are you saying that instead of receiving Price_Change file with a date, you might receive it with period? There will be a slight change, but let's just wait for the requirement to come through and then work on it.

Best,

Sunny