# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Creator III

## PriceList XLS

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??

1 Solution

Accepted Solutions
Master

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

2 Replies
Master

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

Creator III
Author

Works Perfect Mate!!!

Very good 🙂 Thanx

Community Browser