Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Merge fact tables with different granularity

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
CustNamefact1acctno
cust1101
202
303
404
Table2
acctnoId
11a
21a
32b
43c
Table3
Idfact2
1a100
2b200
3c300

Need to merge Table 1 and 3 to create a new column. Appreciate your idea/suggestion on this.

Thanks,

SKar

17 Replies
ziadm
Specialist
Specialist

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;

Anonymous
Not applicable
Author

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.

trdandamudi
Master II
Master II

There is no key field between Table1 and Table2... Can you please share your final output, how it has to look after merge.

jonathandienst
Partner - Champion III
Partner - Champion III

>>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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ziadm
Specialist
Specialist

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

Anonymous
Not applicable
Author

acctno is common between Table 1 and 2.

Anonymous
Not applicable
Author

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.

ziadm
Specialist
Specialist

Please provide a sample of real data and expected output.

Anonymous
Not applicable
Author

something like

   

CustNamefact1(sum of fact1)fact2(sum of fact2)flag[if (fact1/fact2<0.10,'N','Y')]New Column[sum({<flag={'Y'}>}fact1)]
cust1100600Y'100
cust250600N'100

won't be able to post qvw because of security issue.