Mapping ??

[#1]

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

[disqr_rm]

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.

[#3]

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

[fernandotoledo]

Wouldn´t a simple left join do the job?

Load

     StockCode,

     Unit

From Table1;

Left Join Load

     Unit,

     ConversionFactor

From Table 2;

Regards

Fernando

[#5]

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

[Anonymous]

I think this is what you need:

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

Or inner keep load, if you prefer.

[fernandotoledo]

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

[#8]

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

[fernandotoledo]

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

Regards

Fernando

[#10]

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