Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
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
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
