How to calculate a new field during the LOAD

[#1]

Hi,

What is the best way to calculate a new field during the LOAD operation ?

I have a dimension table (Products) that is keyed by DIN with a factor that I would like to use during the loading of my Fact tables.

Directory;

Product:

LOAD DIN, Market, Product_G1, Product_G2, Market.Factor

     from [Dimensions.xlsx] (ooxml, embedded labels, table is Products);

Directory;

Sales:

LOAD @1:15 as DIN, @124:138 as Sales,

     from Sales.txt (fix, record is line, codepage is 1252);

concatenate LOAD @4 as DIN, @7 as Units

     from Units.TXT (txt, codepage is 1252, no labels, delimiter is ',', msq) where exists ( DIN, @4);

It is in the second fact table load (Units.txt) that I need to join to the Product table & create a new field FactoredMarketUnits

as ( Units * Market.Factor ) where Units is @7 in the Units.txt and Market.Factor is from the Product dimension.

Have tried unsuccessfully the JOIN, from_field & an additional LOAD using the resident option.

Could be my syntax !

Thanks for your help.

Aubrey

[Gysbert_Wassenaar · Accepted solution]

Maybe like this:

Directory;

Product:

LOAD DIN, Market, Product_G1, Product_G2, Market.Factor

     from [Dimensions.xlsx] (ooxml, embedded labels, table is Products);

FactorMap:

mapping load DIN, Market.Factor resident Product;

Directory;

Sales:

LOAD @1:15 as DIN, @124:138 as Sales,

     from Sales.txt (fix, record is line, codepage is 1252);

concatenate LOAD @4 as DIN, @7 as Units,

    applymap('FactorMap',@4,0) * @7 as FactoredMarketUnits

     from Units.TXT (txt, codepage is 1252, no labels, delimiter is ',', msq) where exists ( DIN, @4);

[Colin-Albert]

you could use a mapping table to get the link between DIN and Market.Factor from the product table and then apply this to the Sales table. The mapping table will be dropped at the end of the reload.

Directory;

Product:

LOAD DIN, Market, Product_G1, Product_G2, Market.Factor

     from [Dimensions.xlsx] (ooxml, embedded labels, table is Products);

MT_Map:

mapping Load

     DIN,  Market.Factor

resident Product;

Directory;

Sales:

LOAD @1:15 as DIN, @124:138 as Sales,

     from Sales.txt (fix, record is line, codepage is 1252);

concatenate LOAD

     @4 as DIN,

     @7 as Units,

     @7 * applymap('MT_Map', @4, 0) as FactoredMarketUnits

    from Units.TXT (txt, codepage is 1252, no labels, delimiter is ',', msq) where exists ( DIN, @4);

[#4]

Aubrey,

Try this:

Product:

LOAD DIN, Market, Product_G1, Product_G2, Market.Factor

     from [Dimensions.xlsx] (ooxml, embedded labels, table is Products);

//a maaping load is a temp table with 2 fields only. Used with ApplyMap

Map1:

Mapping load  DIN, Market.Factor

  from [Dimensions.xlsx] (ooxml, embedded labels, table is Products);

Sales:

LOAD @1:15 as DIN, @124:138 as Sales,

       from Sales.txt (fix, record is line, codepage is 1252);

//din is the key. I assume that you can have sales only if you have units (left join)

left JOIN(Sales)

   LOAD @4 as DIN, @7 as Units,

applymap('Map1', @4) * @7 as FactorUnits

     from Units.TXT (txt, codepage is 1252, no labels, delimiter is ',', msq) where exists ( DIN, @4);

Fabrice

[#5]

Thanks Gysbert for the helpful & quick response. It worked great

[#6]

Thanks Colin for the helpful & quick response. It worked great

[#7]

Thanks Fabrice for the helpful & quick response. It worked great