Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Multiple join taking really long

Greetings,

I've a table like this

School:

NameClassResult
DiazEnglish12
DiazFrench16
DiazGerman17
HoltEnglish11
HoltFrench8
HoltGerman9
PeraltaEnglish15
PeraltaFrench16
PeraltaGerman20

And i would like to change it to that way:

Final:

NameEnglishFrenchGerman
Diaz121617
Holt1189
Peralta151620

I'm doing it like this:

Final:

load Name

resident School;

left join (Final)

LOAD Name,

Class as English

Resident School

where "Class" = 'English';

left join (Final)

LOAD Name,

Class as French

Resident School

where "Class" = 'French';

left join (Final)

LOAD Name,

Class as German

Resident School

where "Class" = 'German';

It work fine big i've a lot of data and if i try to add more classes (more join), it's taking suddenly a tremendous amount of time, sometimes crashing the qliksense engine, is there something i'm doing wrong like a loop i don't see or something ?

Thanks for the answers!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

JOIN can if it is not done on the right matching fields turn into a multiplication of the rows of the two tables involved. This is called a cartesian join/cartesian product.

So if you do a join with no common fields (common field names) then you get  m x n rows as a result. Every row from the first table matched with every row of the second table. If the first is 10,000 rows and the second is 100,000 rows you will get 1000,000,000 rows unless you run out of memory (or time) ....

View solution in original post

4 Replies
Highlighted
MVP
MVP

JOIN can if it is not done on the right matching fields turn into a multiplication of the rows of the two tables involved. This is called a cartesian join/cartesian product.

So if you do a join with no common fields (common field names) then you get  m x n rows as a result. Every row from the first table matched with every row of the second table. If the first is 10,000 rows and the second is 100,000 rows you will get 1000,000,000 rows unless you run out of memory (or time) ....

View solution in original post

Highlighted
MVP
MVP

You can get what you want by doing this script:

Final:

LOAD

  Name,

  Sum(If( Class = 'English' , Result )) AS English,

  Sum(If( Class = 'French' , Result )) AS French,

  Sum(If( Class = 'German' , Result )) AS German

RESIDENT

  School

GROUP BY

  Name;

Highlighted
Partner
Partner

Thank's Petter for explain!


I think this will help some others people.

Highlighted
Partner
Partner

Please mark the correct answer so the thread gets closed.