Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
subhasmita_kar
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
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

];



subhasmita_kar
Creator
Creator
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?

subhasmita_kar
Creator
Creator
Author

Please suggest me some alternative solutions.

Thank You,

SKar

kuba_michalik
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

];

ElizaF
Creator II
Creator II

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

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

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