topic Unit Conversation in Script in QlikView

Unit Conversation in Script

safik003 — Sat, 16 Nov 2024 01:30:31 GMT

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.

Re: Unit Conversation in Script

safik003 — Wed, 15 Jan 2020 09:45:23 GMT

@sunny_talwar Your valuable input please.

Re: Unit Conversation in Script

marcus_sommer — Wed, 15 Jan 2020 12:19:51 GMT

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