Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Partner
Partner

Yet another cartesian product

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.

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Valued Contributor III

Re: Yet another cartesian product

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;

View solution in original post

2 Replies
Highlighted
Valued Contributor III

Re: Yet another cartesian product

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;

View solution in original post

Highlighted
Partner
Partner

Re: Yet another cartesian product

Yes Sir !!!

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

You made my day.

Thanks,

A