Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

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

];



Creator
Creator

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?

Creator
Creator

Please suggest me some alternative solutions.

Thank You,

SKar

Partner
Partner

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

];

Creator II
Creator II

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

Creator
Creator

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.

Creator
Creator

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.

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.