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 left join to join Table1 to Table2 & Table 3
Table1:
Load fact1,accno
from Table1
left join
load accno,id
from Table2
left join
Load Id,fact2
from Table3;
Can you please load the example tables and share the qvw ? because I am not getting the correct fact2 value when doing left join. sum of fact2 should be 600 not 700.
There is no key field between Table1 and Table2... Can you please share your final output, how it has to look after merge.
>>sum of fact2 should be 600 not 700.
A join will create two entries for the instance of ID = 1a, as this ID has multiple entries in the link table, so a simple sum of fact2 will be 700 (100+100+200+300). You could load the data as is (ie no join) and rely on QV associations, then a simple sum will be 600.
Hi
I can see that there is acctno is the common field between Table1 and Table2 .. is the correct ?
if no relation then you cannot join and you have to concatenate Table1 & Table3
acctno is common between Table 1 and 2.
I understand, but I will need to merge the tables to create a new column which uses fact1 and fact2 columns. I need to create flag which uses the fact columns, tried to do on UI. But on UI, I have limitation of using calculation on left side of a modifier in set analysis.
Please suggest some solutions.
Please provide a sample of real data and expected output.
something like
CustName | fact1(sum of fact1) | fact2(sum of fact2) | flag[if (fact1/fact2<0.10,'N','Y')] | New Column[sum({<flag={'Y'}>}fact1)] |
cust1 | 100 | 600 | Y' | 100 |
cust2 | 50 | 600 | N' | 100 |
won't be able to post qvw because of security issue.