Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Most solutions I've found on how to simulate a cross join in Qlikview involved using a full outer on tables that don't share a commonly named field and then trim the rows in excess.
What I am curious to find out is whether the same could be achieved using two for loops nested one in the other. I am not interested in whether it is a more/less efficient solution here, just in the technical feasibility.
In very-pseudo-code it would be something like:
for each row in table1
return field1
(string) concatenate field 1 &
for each row in table2
return field2
This should give us a table with the desired cardinality and just one field. Then we could use left() and right() to pull out the original fields.
Unfortunately I struggle putting this into proper Qlikview syntax, any idea?
Hi Igor,
Hope I understood it correctly, I think you can fix this as follows:
Table1:
field1
FROM Table1;
LEFT JOIN (Table1) LOAD
field2
FROM Table2;
Table3:
NOCONCATENATE LOAD
field1&'|'&field2 AS NewField
RESIDENT Table1;
DROP TABLE Table1;
In this case you will get every possibility from field2 for each field 1.
to untie the fields you can use: SUBFIELD(NewField,'|',1) (for field1) en SUBFIELD(NewField,'|',2) for field2
for each loops will be much slower than joins.