topic Re: Mapping ?? in QlikView

Mapping ??

Mon, 09 May 2011 23:56:59 GMT

Probably a mapping solution but I'm not sure how to do it.

I have two tables, both with stock code numbers. The first table has a descritpion field for Base Unit of Measure such as "BAG", "PAK", "FT" . . and for each stock code there is only one Base Unit of Measure. The second table will list the stock code mulitple time for the Unit of Measure because it holds a conversion factor. So an item in the second table for part "A" may have:

Unit of Measure - Conversion Factor

FT - 1000

EA - 1

M - 1525

I need to produce one table that shows the following:

Stock Code - Unit of Measure(table 1) - Conversion factor (table two)

A (from table 1) - FT (from table 1) - 1000 (from table 2 but I only want the conversion factor where the Unit of Measure is FT)

Thanks,
Stephen

Mapping ??

disqr_rm — Tue, 10 May 2011 00:03:29 GMT

YOu need to define a mapping table for the conversion multiplier values, like:

mapConversion:

MAPPING

LOAD UOM, ConversionFactor FROM ....

Then in your main table you can multiply using ApplyMap function, like:

Table:

LOAD a, b, c, UOM, Amount,

Amount * applymap('mapConversion', UOM, 1) as ConvertedAmount

FROM.....

The "1" will make sure that if you do not have a conversion value for a UOM, it will by default multiply with 1, making it same value. Depends how you would like it, you can also use 0 so the result becomes 0 too.

Hope this helps.

Mapping ??

Tue, 10 May 2011 19:34:28 GMT

Still can get it to work. I am not trying to calculate anything, I am trying to add a field that is table 2 to table 1.

In the second table (which I load first) I use a WHERE UnitOfMeasure = "FT" to get only the single conversion factor I need ( ie 1000). I then want to add the Conversion Factor to table 1 as a new field.

Stephen

Mapping ??

fernandotoledo — Tue, 10 May 2011 22:47:13 GMT

Wouldn´t a simple left join do the job?

Load

StockCode,

Unit

From Table1;

Left Join Load

Unit,

ConversionFactor

From Table 2;

Regards

Fernando

Mapping ??

Tue, 10 May 2011 23:10:53 GMT

Fernando,

Hope I explain this OK. The left join would work for my "FT" example. But I have "BAG" and "PAK" and a few other Unit of Measures that I need to also extract the look up the Conversion Factor. So my tables look more like below.

Table 1

Stock Code - Unit of Measure

A - FT

B - BAG

C - PAK

Table 2

Stock Code - Unit of Measure - Conversion Factor

A - EA - 1

A - FT - 1000

A - M - 1525

B - EA - 1

B - BAG - 25

C - EA - 1

C - PAK -50

The end result table I need is:

Stock Code - Unit of Measure - Conversion Factor

A - FT - 1000

B - BAG - 25

C - PAK - 50

Hope this helps explain what I need.

Thanks,
Stephen

Mapping ??

Anonymous — Wed, 11 May 2011 00:39:01 GMT

I think this is what you need:

table2:
LOAD * Resident ...
where exists("Unit of Measure")

Or inner keep load, if you prefer.

Mapping ??

fernandotoledo — Wed, 11 May 2011 10:56:42 GMT

Left join Table 2 into Table 1 will do that, since it will bring only the combinations of Stock Code and Unit of Measure that apears on table 1. Just like our friend Solomovich suggested using Exists() clause.

Regards

Fernando

Mapping ??

Wed, 11 May 2011 15:30:44 GMT

Sorry I have table 2 above wrong for "B" and "C" in the Conversion Factor field. It is:

Table 2

Stock Code - Unit of Measure - Conversion Factor

A - EA - 1

A - FT - 1000

A - M - 1525

B - EA - 25

B - BAG - 1

C - EA - 50

C - PAK - 1

So, your solution above would work for stock code "A" but not for "B" and "C". So, for Stock Code "B", Table 1 is BAG and I need the EA conversion factor value from table 2 (25). Similarly for Stock Code "C" I need the EA conversion factor of 50.

Stephen

Re: Mapping ??

fernandotoledo — Wed, 11 May 2011 17:10:02 GMT

I think the result is what you need. See the QVW atached.

Regards

Fernando

Mapping ??

Wed, 11 May 2011 17:31:25 GMT

Fernando,

In your Inline load you have Part "B" loading correctly, but notice that result with left join returns a 1, I need returned 25. For part "C" you have the EA and PAK amount reversed. Putting them correctly also returns a 1 when I need returned 50.

What the quantities mean is that we sell "1" PAK that has "50" EA inside the PAK.

Stephen

Re: Mapping ??

fernandotoledo — Wed, 11 May 2011 19:05:42 GMT

Now I got it!

Stock Code	Unit of Measure	From Unit	From Factor	To Unit	To Factor
A	FT	FT	1000	EA	1
B	BAG	BAG	1	EA	25
C	PAK	PAK	1	EA	50

See the atached file again!

You have to create a sequence of joins to create a From Unit - To Unit logic.

Mapping ??

Wed, 11 May 2011 19:16:02 GMT

Fernando,

First, thanks for your help. We are almost there. In the last column (To Factor) I need the FT item to show 1000. In other words, I need the EA quantity for the items "BAG" and "PAK" (as you have it) but I need the "FT" quantity for the FT item.

Thanks,
Stephen

Re: Mapping ??

fernandotoledo — Wed, 11 May 2011 19:32:31 GMT

That´s it! I put a final load statement to filter only de desirable conversions in your table!

Mapping ??

Wed, 11 May 2011 19:37:48 GMT

Exactly what I need!! Thank you Frenando you have been a huge help to me.

Stepen

Mapping ??

fernandotoledo — Wed, 11 May 2011 20:03:18 GMT

Glad I could help!

Dont forget to mark the correct answer for the other users who might find this topic helpfull.

Best regards!

Fernando