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
ElizaF
Creator II
Creator II

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

];



Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

Please suggest me some alternative solutions.

Thank You,

SKar

kuba_michalik
Partner - Specialist
Partner - Specialist

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

];

ElizaF
Creator II
Creator II

Sorry, but I do not understand your requirement. Please can you send me more information and upload the file.

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

ElizaF
Creator II
Creator II

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.