Skip to main content

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 
Search instead for 
Did you mean: 
johnan
Creator III
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:

customerNumberDatePallets
30692020-09-015
30692020-09-0422
30702020-09-1221

 

Then i have an EXCEL with a price per Pallet.

CUSNBPALLETSNamEPrice
30694XX330
30695XX264
30696XX264
30697XX264
30698XX171
30699XX171
306910XX171
306911XX153
306912XX153
306913XX137
306914AXX137
306922XX136
307021XX136

 

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
edwin
Master
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 264Capture.PNG

View solution in original post

2 Replies
edwin
Master
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 264Capture.PNG

johnan
Creator III
Creator III
Author

Works Perfect Mate!!!

Very good 🙂 Thanx