Mapping using 2 mapping tables

[Anonymous]

Hello Experts,

A simple question but cant find its answer!

Can we create a column using 2 mapping tables? let me explain what I want...

In total I have 1 data table and 2 mapping tables with 2 columns in each of them.

Data Table

[ Category ] and [ Sale ]

Mapping tables

Table 1

[ From_Filter 1 ] and [ To_Filter 1 ]

Table 2

[ From_Filter 2 ] and [ To_Filter 2]

Now, I want to create another column in the Data Table called Product Group. This Product Group column will be based on differnet conditions using two mapping tables.

Below are the conditions I want to be met while I create Product Group column in data table:

Product Group should be = Enerygy IF From_Filter 1 = Energy and From_Filter 2 = OIL

Product Group should be = Enerygy IF From_Filter 1 = Energy and From_Filter 2 = GAS

Product Group should be = Asset IF From_Filter 1 = Property and From_Filter 2 = APS

Product Group should be = Asset IF From_Filter 1 = Property and From_Filter 2 = PROPERTY

etc

I can do this using if conditions but the problem is that these conditions are too many and I cant use if condition for all of them. Thats why I created mapping tables.

Can someone please help me in this?

Thanks

[yasus]

How about using only 1 mapping table based on a concatenation of both criterias within your load script?

Data Table

Load *,

Category&Sale as [Product Group] //Mapping Criteria

Mapping tables

Table 1:

LOAD * INLINE [

    x, y

    EnergyOIL, Enerygy

    EnergyGAS, Enerygy

    PropertyAPS, Asset

    PropertyPROPERTY, Asset

// etc.

];

[Anonymous]

Hi Gregor,

Thanks for your reply!

I think I didnt explain properly what I am trying to achieve.

Actually I have got 1 table it has 2 Columns. Primary Hierarchy and Secondary Hierarchy. Based on these two columns I want to create another one. Which I would call it as Product Group. So I thought the best way to do it is to export Primary Hierarchy and Secondary Hierarchy and make them as mapping tables. May be this is not correct. May be the solution is something else. I have attached a snapshot of what I want to clear this. Please see attached.

For Energy its simple. Regarless of what Secondary Hierarchy has, Product Group will always be Energy. But for EMP I will need to use Secondary Hierarchy. So If Primary is EMP and Secondary is New Assest then Product Group should be ALP but if Primary is EMP and Secondary its Old Asset then Product Group should be OST.

So on and so forth. I hope this time I have explained it properly. how can I solve this problem? Do I have to use mapping tables?

Please help!

Thanks

[yasus · Accepted solution]

Maybe I explanation was not clear enough, but the following code achives exactly what you are looking for:

Code:

MAP:
mapping load * Inline [
     x, y
     EnergyAVI Liability, Energy
     EnergyAVI Product, Energy
     EnergyAVI CRS, Energy
     EMPNew Asset, ALP
     EMPOld Asset, OST
     ARSOIL, Property
     ARSGAS, Property
     ARSAPS, Casualty
     ];
map [Product Group] using MAP;
Data:
Load * Inline [
Primary Hierarchy, Secondary Hierarchy, Sale
Energy, AVI Liability, 23
Energy, AVI Product, 34
Energy, AVI CRS, 45
EMP, New Asset, 67
EMP, Old Asset, 34
ARS, OIL, 23
ARS, GAS, 45
ARS, APS, 76
];
MappedData:
LOAD *,
[Primary Hierarchy] & [Secondary Hierarchy] as [Product Group] 
resident Data; 
drop table Data;

Result:

[Anonymous]

Thank you so much Gregor!

it worked perfectly fine