Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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) ....
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) ....
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;
Thank's Petter for explain!
I think this will help some others people.
Please mark the correct answer so the thread gets closed.