Multiple join taking really long

[Anonymous]

Greetings,

I've a table like this

School:

Name	Class	Result
Diaz	English	12
Diaz	French	16
Diaz	German	17
Holt	English	11
Holt	French	8
Holt	German	9
Peralta	English	15
Peralta	French	16
Peralta	German	20

And i would like to change it to that way:

Final:

Name	English	French	German
Diaz	12	16	17
Holt	11	8	9
Peralta	15	16	20

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!

[petter · Accepted solution]

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) ....

[petter]

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;

[breno_morais]

Thank's Petter for explain!

I think this will help some others people.

[bwisealiahmad]

Please mark the correct answer so the thread gets closed.