Mapping multiple fields of multiple tables and finding matching value

[Anonymous]

Hi All,

I have FACTS table with date key & customer key. Two different Dimension tables customer table with customer key and time  table with date key and respective month. Another dimension table with targets, month and customer area Key.

Now i want to load target value into facts table by mapping month value from facts with dimension target table field month and customer area filed of customer dimension with customer key.

Table Facts:

Datekey,

Customer key

Table Customer Dimension:

Customer key,

Customer Area

Table Target Dimension:

CustomerArea,

Month,

Target

Output i required is:

Table Facts:

DateKey,

CustomerKey,

month from datekey,customer area(Customerkey from Table CustomerDimension) and get Target of 'Table Target Dimension' as Target.

Could some one suggest me about data model or how to get target field into Facts table?

Thanks to sugget.

Regards,

Krishna Chaitanya B

[Anonymous]

Hi Sachin,

Thanks for reply. Yesterday i was bit busy with other projects, Today i will try this and update you.

Regards,

Krishna

[Anonymous]

Hi Rahul,

I have to tables as below

Table 1
Col1	Col2	Col3
A	1	200
B	3	100
C	4	400
D	5	500
A	2	500
D	3	300
C	5	600

Table 2
Col1	Col2
A	1
D	5
D	3
C	4

Could you suggest me how could i do look up or any other way to get COL3 value of table 1 on matching with both column values of table 2?

Output should be:

Table 3
Col1	Col2	Col3
A	1	200
D	5	500
D	3	300
C	4	400

Thanks to advice.

Regards,

Krishna

[Anonymous]

Hi Sachin,

Now data model is changed and i am trying to get solution for below scenario. Thanks to advice.

Hi Rahul,

I have to tables as below

Table 1
Col1	Col2	Col3
A	1	200
B	3	100
C	4	400
D	5	500
A	2	500
D	3	300
C	5	600

Table 2
Col1	Col2
A	1
D	5
D	3
C	4

Could you suggest me how could i do look up or any other way to get COL3 value of table 1 on matching with both column values of table 2?

Output should be:

Table 3
Col1	Col2	Col3
A	1	200
D	5	500
D	3	300
C	4	400

Thanks to advice.

Regards,

Krishna

[rahulpawarb]

Hello Krishna,

You can me use of below given sample script to create desired output (Final Data Model will contain only Table2; if you want Table1 for further use then create Mapping table using resident load - Refer commented code in the script).

Table1:
Mapping LOAD 
AutoNumber(Col1 & '|' & Col2, 'Key') AS Key,
Col3;
LOAD * INLINE [
Col1, Col2, Col3
A, 1, 200
B, 3, 100
C, 4, 400
D, 5, 500
A, 2, 500
D, 3, 300
C, 5, 600
];


Table2:
LOAD *,
     ApplyMap('Table1', AutoNumber(Col1 & '|' & Col2, 'Key')) AS Col3;
LOAD * INLINE [
Col1, Col2
A, 1
D, 5
D, 3
C, 4
];

Refer the attached sample application.

Thank you!

Rahul

[Anonymous]

Hi Rahul,

Thanks Alot. But final one question. Is it possible to mention some default value if match does not find.

Regards,

Krishna

[rahulpawarb · Accepted solution]

Hello Krishna,

Yes we can pass default value. Please refer below given syntax and example (If no match found then -999 will assigned to Col3).

//Syntax: ApplyMap('MappingTableName', MappingColumn, DefaultValue) AS ColumnName
ApplyMap('Table1', AutoNumber(Col1 & '|' & Col2, 'Key'), -999) AS Col3

Hope this will be of help.

Regards!

Rahul

[Anonymous]

Thank You Rahul.

[rahulpawarb]

Cheers,

Rahul