topic Re: Merge fact tables with different granularity in QlikView

Merge fact tables with different granularity

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Merge fact tables with different granularity

ziadm — Thu, 08 Dec 2016 22:18:49 GMT

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;

Re: Merge fact tables with different granularity

Anonymous — Fri, 09 Dec 2016 03:39:32 GMT

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.

Re: Merge fact tables with different granularity

trdandamudi — Fri, 09 Dec 2016 04:25:42 GMT

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

Re: Merge fact tables with different granularity

jonathandienst — Fri, 09 Dec 2016 05:29:40 GMT

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

Re: Merge fact tables with different granularity

ziadm — Fri, 09 Dec 2016 05:30:55 GMT

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

Re: Merge fact tables with different granularity

Anonymous — Fri, 09 Dec 2016 23:12:04 GMT

acctno is common between Table 1 and 2.

Re: Merge fact tables with different granularity

Anonymous — Fri, 09 Dec 2016 23:15:26 GMT

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.

Re: Merge fact tables with different granularity

ziadm — Fri, 09 Dec 2016 23:22:24 GMT

Please provide a sample of real data and expected output.

Re: Merge fact tables with different granularity

Anonymous — Fri, 09 Dec 2016 23:35:17 GMT

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.

Re: Merge fact tables with different granularity

ElizaF — Sat, 10 Dec 2016 09:52:13 GMT

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

];

Re: Merge fact tables with different granularity

Anonymous — Sun, 11 Dec 2016 20:22:09 GMT

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?

Re: Merge fact tables with different granularity

Anonymous — Sun, 11 Dec 2016 20:37:41 GMT

Please suggest me some alternative solutions.

Thank You,

SKar

Re: Merge fact tables with different granularity

kuba_michalik — Mon, 12 Dec 2016 08:45:09 GMT

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

];

Re: Merge fact tables with different granularity

ElizaF — Tue, 13 Dec 2016 08:01:08 GMT

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

Re: Merge fact tables with different granularity

Anonymous — Tue, 13 Dec 2016 20:10:37 GMT

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.

Re: Merge fact tables with different granularity

Anonymous — Tue, 13 Dec 2016 20:15:11 GMT

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.

Re: Merge fact tables with different granularity

ElizaF — Wed, 14 Dec 2016 06:57:13 GMT

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.