topic Re: Linking 2 fact tables in QlikView

Linking 2 fact tables

Anonymous — Mon, 16 Dec 2013 16:33:18 GMT

Hello Friends,

I feel the solution is simple, I am simply very tired for now and could you a little help...

I have the following scenario:

First Fact

T1:

LOAD * INLINE [

A, B, C

x, 2, 4

y, 5, 7

z, 9, 11

]

;

T2:

LOAD * INLINE [

A, J, K

x, j1, 0.5

x, j2, 0.3

x, j3, 0.2

x, j4, 0.3

x, j5, 0.4

x, j6, 0.3

y, j7, 0.5

y, j8, 0.3

y, j9, 0.2

y, j10, 0.3

y, j11, 0.4

y, j12, 0.3

z, j71, 0.5

z, j81, 0.3

z, j91, 0.2

z, j101, 0.3

z, j111, 0.4

z, j121, 0.3

]

;

I would like to combine these 2 table to have one table with the following calculation:

U = K*B and V = K*C

T3:

LOAD * INLINE [

A, J, K, U, V,

x, j1, 0.5, 0.5*2, 0.5 *4

x, j2, 0.3, K*B, K*C

x, j3, 0.2 , K*B, K*C

x, j3, 0.2, K*B, K*C

x, j4, 0.3 etc....

x, j5, 0.4 etc....

x, j6, 0.3 etc....

y, j7, 0.5 etc....

y, j8, 0.3 etc....

y, j9, 0.2

y, j10, 0.3

y, j11, 0.4

y, j12, 0.3

z, j71, 0.5

z, j81, 0.3

z, j91, 0.2

z, j101, 0.3

z, j111, 0.4

z, j121, 0.3

]

;

Many thanks,

Antoine

Re: Linking 2 fact tables

Nicole-Smith — Mon, 16 Dec 2013 16:37:41 GMT

T1:

LOAD * INLINE [

A, B, C

x, 2, 4

y, 5, 7

z, 9, 11

];

left join (T1)

LOAD * INLINE [

A, J, K

x, j1, 0.5

x, j2, 0.3

x, j3, 0.2

x, j4, 0.3

x, j5, 0.4

x, j6, 0.3

y, j7, 0.5

y, j8, 0.3

y, j9, 0.2

y, j10, 0.3

y, j11, 0.4

y, j12, 0.3

z, j71, 0.5

z, j81, 0.3

z, j91, 0.2

z, j101, 0.3

z, j111, 0.4

z, j121, 0.3

];

left join (T1)

load *,

K*B as U,

K*C as V

resident T1;

drop fields B, C from T1;

Re: Linking 2 fact tables

Anonymous — Mon, 16 Dec 2013 17:05:39 GMT

EDIT:

/*Managed to solve it via this post:

Rangesum aggregation script

Sorry Nicole, was too quick.

If you observe, in the solution, I get double the amount, since x contains basically 200% of the value.

How could I spread while respecting the global 100% ratio?

EDIT

Great Nicole!!!

Many thanks,

Antoine