Yet another cartesian product

[Anonymous]

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.

[olivierrobin · Accepted solution]

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;

[Anonymous]

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