Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
imark
Contributor III
Contributor III

Cross join through nested for loops?

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?

2 Replies
avkeep01
Partner
Partner

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

Gysbert_Wassenaar

for each loops will be much slower than joins.


talk is cheap, supply exceeds demand