Unit of Measurement Conversions

[#1]

Is there a sure-fire way to do UOM Conversions dynamically within QlikView assuming you had a conversion table? There would be a base unit that have "Always One Selected Value."  Also, can you do a two step/multi step conversion?

For example:

100 CM -> 1M

1000M -> 1KM

but the conversion from CM -> KM does not exist, can you convert a CM to KM by CM -> M -> KM?

Also, is there a way to get the conversion for a product with that being the only dimension? In attached example, I had to add some extra dimension to get it to work. From what I understand, set analysis only works on a table/chart level and not the dimensions within it, so when I tried sum(Quantity)*if(Unit = TO, 1, only({<FROM = {$(=Unit)}>} Conv)), it would not work as it wasn't processing the unit on a per-product basis.

Thanks in advance,

Alex

[anbu1984]

Added below row to table UoMConv

LBS,LBS,1

Changed Unit to FROM in second table

[#3]

Doesn't convert Product C to LBs though.

I'd need a way to either recursively permutate through each possible combination (with TO-FROM and FROM-TO as a key with 1/Conv and Conv respectively) and apply a map where necessary to convert the "missing" conversion and load only distinct values so everything has a 1:1 ratio.

In this example, eventually that should map out FT-MFT and MFT-LBS as 1000*.05 = 50 for that conversion.