Unit Conversation in Script

[safik003]

I have the following the following two tables . one is Unit Conversion and second one is product.

Unit_Conv:

Unit_Conv:
Load * INLINE[
PriceUnt, QuantityUnt, ConversationRate
$/liter, gal, 3.78
$/gal, liter, 0.264
$/gal, ml, 0.0002
];

Product:

Load * INLINE [
Product, ProductID, Price, PriceUnit, Quantity, QuantityUnit
Diesel, 1, 5, $/liter, 20, gal
Petrol, 2, 8, $/gal, 30, liter
CNG, 3, 6, $/gal, 40, ml
];

To get the total cost of a product the unit needs to  be converted according to the unit of Quantity then it should be multiplied by QuantityUnit.

For example, to get the total cost of Diesel the PriceUnit needs to be converted from $/liter to $/gal as QuantityUnit is gal. So the total cost of Diesel will be =  5 * 3.78(Unit Conversion rate) * 20 = 378 $.

How to achieve this in Qlik Script? Suggestion please. Test file is attached.

[safik003]

@sunny_talwar Your valuable input please.

[marcus_sommer · Accepted solution]

Maybe with an approach like this one:

Unit_Conv:
mapping load PriceUnt & '|' & QuantityUnt, ConversationRate;
Load * INLINE[
PriceUnt, QuantityUnt, ConversationRate
$/liter, gal, 3.78
$/gal, liter, 0.264
$/gal, ml, 0.0002
];

Product:
load *, applymap('Unit_Conv', PriceUnit & '|' & QuantityUnit, 0) * Price as TotalCosts;
Load * INLINE [
Product, ProductID, Price, PriceUnit, Quantity, QuantityUnit
Diesel, 1, 5, $/liter, 20, gal
Petrol, 2, 8, $/gal, 30, liter
CNG, 3, 6, $/gal, 40, ml
];

- Marcus