topic Re: PriceList XLS in QlikView

PriceList XLS

johnan — Thu, 10 Sep 2020 13:51:13 GMT

Hi Forum!

I have an app there i calculate pallets (Quantity/EachPallet) in a chart/Staight Table

I have an identity like customerNumber, le'ts say 3069
So my reslut will be like:

customerNumber	Date	Pallets
3069	2020-09-01	5
3069	2020-09-04	22
3070	2020-09-12	21

Then i have an EXCEL with a price per Pallet.

CUSNB	PALLETS	NamE	Price
3069	4	XX	330
3069	5	XX	264
3069	6	XX	264
3069	7	XX	264
3069	8	XX	171
3069	9	XX	171
3069	10	XX	171
3069	11	XX	153
3069	12	XX	153
3069	13	XX	137
3069	14	AXX	137
3069	22	XX	136
3070	21	XX	136

NOw i want to present for 3069, 2020-09-01, 5 pallets (price is 264 per pallet) * Pallets It should be 1320
And next 22*136 = 2992
And next: 21*136 = 2856

I was thinking in my expression like this
Sum(If(Quantity/EachPallet = PALLETS,*Price*Pallets))

Mabye aggr??

Re: PriceList XLS

edwin — Thu, 10 Sep 2020 20:17:30 GMT

This is actually a great test case. APplymap exists at script/load time but no equivalent in chart/expression run time. ive seen some propose a Pick Match, but the problem with that is its serial (1,2,3,..) which does not apply here as the first value is 5.

i propose a brute force approach as i cant think of an out of the box functionality. use subfile and create a value pair to lookup:

in your chart, create an expression labeled Order (this is important as we will refer to it):
Order: sum(Pallets)
this expression looks up the price:
=subfield(SubField( ','&Concat(PALLETS & '|' & Price,','),','&[Order]&'|',-1),',',1)
this expression gives total amount:
=subfield(SubField( ','&Concat(PALLETS & '|' & Price,','),','&[Order]&'|',-1),',',1) * [Order]

this builds a list of PALLETS and its Price delimited by , per palletPrice and the Price is separated by pipe |
so for customer 3069:

,10|171,11|153,12|153,13|137,14|137,22|136,4|330,5|264,6|264,7|264,8|171,9|171

which means PALLETS=10|Price=171, PALLETS=4|Price=330

to lookup a valuePair for say 5 PALLETS: use subfield function with ,5 as the delimeter and -1 as position. it will return 264,8|171,9|171 then apply another subfield with ',' as delimeter you get 264

Re: PriceList XLS

johnan — Fri, 11 Sep 2020 07:41:29 GMT

Works Perfect Mate!!!

Very good 🙂 Thanx