topic Cross join through nested for loops? in QlikView

Cross join through nested for loops?

imark — Thu, 30 Aug 2018 11:01:46 GMT

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?

Re: Cross join through nested for loops?

avkeep01 — Thu, 30 Aug 2018 11:13:41 GMT

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

Re: Cross join through nested for loops?

Gysbert_Wassenaar — Thu, 30 Aug 2018 11:15:30 GMT

for each loops will be much slower than joins.