topic Re: Unit of measure conversion help in QlikView

Unit of measure conversion help

Tue, 23 Sep 2014 13:19:37 GMT

I need help with converting units of measure. I need the ability to see the measures in litres or cases.

Currently, all my measures are coming into Qlikview with the hectolitre measurement. It is easy for me to change all of it to litres because it is a 1 to 1 relationship between litres and hectolitres. However, I have different conversion to change it into cases because each package has a different conversion.

I have a table that gives me the conversion for each product in liters and cases. What is the best way to convert this or do this so that I have the ability to change the coming numbers from hectolitres to cases or liters.

Re: Unit of measure conversion help

crusader_ — Tue, 23 Sep 2014 13:31:38 GMT

Hi,

Use mapping load of your Conversion table for each product (if it has format Product - Conversion) and ApplyMap() function in script to create new field cases. The same thing with litres.

In the end you will get all possible formats (hectolitres/litres/cases) in different fields and then you'll be able to use all of them as you want.

Hope this helps.

Andrei

Re: Unit of measure conversion help

Tue, 23 Sep 2014 13:35:39 GMT

do you have an example app for this? i'm not familiar with ApplyMap function..that's why

Re: Unit of measure conversion help

demoustier — Tue, 23 Sep 2014 13:40:36 GMT

Hi,

Use Mapping Load and Apply Map function.

Post your conversion table for example

Re: Re: Unit of measure conversion help

demoustier — Tue, 23 Sep 2014 13:55:45 GMT

Hi,

I suppose you have a Data Table with your Product, Quantity and Unit. Somethoing like this:

Data

Product	Unit	Qty
water	bottle	6
alchool	liter	5
juice	pieces	45

You should also have a table with correspondance between Unit and your final measurmentrequired like below:

transco:

Unit	In liter
bottle	5
liter	1
pieces	12

so you can use Aplly Map function:

Mapping

transco:

Load * resident transco;

Load

Product,

Qty*ApplyMap('transco',Unit) as Qty_in_liter

resident Data;

Re: Unit of measure conversion help

crusader_ — Tue, 23 Sep 2014 13:58:43 GMT

Small example:

some_map:

MAPPING LOAD * INLINE [

Product, Litres

milk, 1.3

fuel, 1.4

gas, 0.9

];

Table:

LOAD *

, ApplyMap('some_map',Product,0)*Hectalitres as Litres //Means Hectalitres*convertion=Litres

;

LOAD * INLINE [

Product, Hectalitres

milk, 1400

fuel, 900

gas, 833

];

I used Precending LOAD with Applymap function - the best way to escape several useless same resident loads.

Hope this helps.

Andrei