Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Need your help on merging two fact tables with different granularity. I need to merge the tables to create a new column using facts from those two tables.
I have scenario like below. I have Table1 & 3 as fact tables. Table2 is an intermediate table which holds the link between fact tables.
Table1 | ||
CustName | fact1 | acctno |
cust1 | 10 | 1 |
20 | 2 | |
30 | 3 | |
40 | 4 | |
Table2 | ||
acctno | Id | |
1 | 1a | |
2 | 1a | |
3 | 2b | |
4 | 3c | |
Table3 | ||
Id | fact2 | |
1a | 100 | |
2b | 200 | |
3c | 300 |
Need to merge Table 1 and 3 to create a new column. Appreciate your idea/suggestion on this.
Thanks,
SKar
Hi,
Use applymap function. Try this:
Table2:
Mapping
LOAD * INLINE [
acctno,Id
1,1a
2,1a
3,2b
4,3c
];
Table3:
Mapping
LOAD * INLINE [
Id,fact2
1a,100
2b,200
3c,300
];
Table1:
LOAD
*,
ApplyMap('Table3',Id,0) as fact2;
LOAD
*,
ApplyMap('Table2',acctno,0) as Id;
LOAD * INLINE [
CustName,fact1,acctno
cust1,10,1
cust1,20,2
cust1,30,3
cust1,40,4
];
Thanks for your response. Applymap would definitely help me in merging. But my concern is to get fact2 as 600 for cust1 after merging. Could you please suggest?
Please suggest me some alternative solutions.
Thank You,
SKar
Just drop the second mapping. Then tables 1 and 3 will be associated by Id, and sum of fact2 will be what you want.
Table2:
Mapping
LOAD * INLINE [
acctno,Id
1,1a
2,1a
3,2b
4,3c
];
Table3:
LOAD * INLINE [
Id,fact2
1a,100
2b,200
3c,300
];
Table1:
LOAD
*,
ApplyMap('Table2',acctno,0) as Id;
LOAD * INLINE [
CustName,fact1,acctno
cust1,10,1
cust1,20,2
cust1,30,3
cust1,40,4
];
Sorry, but I do not understand your requirement. Please can you send me more information and upload the file.
Thank you for your response!, my main motive was to merge tables to create a new column using those fact columns and not altering the base aggregated fact value for a customer.
Because of security reason, am not able to attach any files.
I need to simply merge the tables to create a new column using facts, but base aggregated fact value should not change. If you analyze in my tables in example, for cust1 I have fact2 sum as 600 not 700.
But due to different granularity am not getting the correct amount after joining/mapping.
In this case, the solution is the one posted by Jakub Michalik above.
Drop the second mapping and then tables 1 and 3 will be associated by Id. The sum of fact2 for cust1 will be what you want.