topic Re: Yet another cartesian product in QlikView

Yet another cartesian product

Anonymous — Mon, 14 May 2018 13:14:51 GMT

Hello,

I'm starting from this simple tables:

T1:
LOAD * INLINE [
A, B, C, D,
a, b, 3, u,
a, a, 1, u,
b, a, 5, u,
b, b, 1, u,
];

T2:
LOAD * INLINE [
A, B, C, D,
g, h, 4, u,
h, g, 6, u,
g, g, 1, u,
h, h, 1, u,
];

And I want to end up with the following square end matrix:

T1:
LOAD * INLINE [
A, B, C, D,
a, b, 3, u,
a, a, 1, u,
b, a, 5, u,
b, b, 1, u,
g, h, 4, u,
h, g, 6, u,
g, g, 1, u,
h, h, 1, u,

a, h, NULL, u,

a, g, NULL, u,

b, g, NULL, u,

b, h, NULL, u,

g, b, NULL, u,

g, a, NULL, u,

h, a, NULL, u,

h, b, NULL, u,

];

Thanks for your help

Antoine

ps: if possible, paste the code or the logic required.

Re: Yet another cartesian product

olivierrobin — Mon, 14 May 2018 14:00:15 GMT

hello

is the reformulation of your problem :

let's make cartesien product of distinct values of columns a et b from the 2 tables

if column c has a value for a couple, let's take that value, else NULL

and column D is always u ?

So I did that :

T1:
LOAD * INLINE [
A, B, C, D,
a, b, 3, u,
a, a, 1, u,
b, a, 5, u,
b, b, 1, u,
];

//T2:
concatenate(T1)
LOAD * INLINE [
A, B, C, D,
g, h, 4, u,
h, g, 6, u,
g, g, 1, u,
h, h, 1, u,
];

T3:
noconcatenate
load distinct A resident T1;
join(T3)
load distinct B resident T1;
left join(T3)
first 1
load D resident T1;

left join(T3)
load A,B,C
resident T1;
drop table T1;

exit script;

Re: Yet another cartesian product

Anonymous — Mon, 14 May 2018 17:03:53 GMT

Yes Sir !!!

Yes, it is always U when the need is to attain a square matrix in the end.

You made my day.

Thanks,