Group by explanation

[Anonymous]

Greetings,

I've a table like this

School:

Name	Class	Teacher
Diaz	English	Santiago
Diaz	French	Terry
Diaz	German	Scully
Holt	English	Santiago
Holt	French	Terry
Holt	German	Scully
Peralta	English	Santiago
Peralta	French	Hitchcock
Peralta	German	Scully

And i would like to change it to that way:

Final:

Name	English	French	German
Diaz	Santiago	Terry	Scully
Holt	Santiago	Terry	Scully
Peralta	Santiago	Hitchcock	Scully

I'm doing it like this:

Final: 

LOAD  Name, 

If( Class = 'English' , Teacher ) AS English, 

If( Class = 'French' , Teacher ) AS French, 

If( Class = 'German' , Teacher) AS German 

RESIDENT  School ;

But it give me this instead:

Name	English	French	German
Diaz	-	-	-
Diaz	Santiago	-	-
Diaz	-	Terry	-
Diaz	-	-	Scully
Holt	-	-	-
Holt	Santiago	-	-
Holt	-	Terry	-
Holt	-	-	Scully
Peralta	-	-	-
Peralta	Santiago	-	-
Peralta	-	Terry	-
Peralta	-	-	Scully

I tried group by but i need an agregation function.

Thanks for the answers!

[sewialwork]

Try to use Crosstable function (https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/LoadData/work-with-cross-tables.ht...).

Or you can use this example Multiple rows with different dimensions

Hope this will help)

AGB

[Arthur_Fong]

Hi Vincent,

Instead of using Crosstable, you should apply Generic Table.

Refer to the attachment below to see how it works.

Thanks and regards,

Arthur Fong

[OmarBenSalem]

Please refer to this :

The Generic Load

[Arthur_Fong]

Hi Vincent,

Didn't notice that you are referring to Qlik Sense, but the load script has no different with Qlik View.

Refer attached is the file in qvf (Qlik Sense).

Thanks and regards,

Arthur Fong

[Anonymous]

Hey Omar,

my table is actually really huge and it's taking forever to load it with Generic isn't there any other way ?

[Anonymous]

Hey Arthur,

my table is actually really huge and it's taking forever to load it with Generic isn't there any other way ?

[petter · Accepted solution]

You can use the Concat() string concatenation aggregation function along with GROUP BY:

Final: 
LOAD  Name, 
Concat( If( Class = 'English' , Teacher ) ) AS English, 
Concat( If( Class = 'French' , Teacher ) ) AS French, 
Concat( If( Class = 'German' , Teacher) ) AS German 
RESIDENT  School
GROUP BY
  Name;

Actually MinString(), MaxString() and Only() should work just as well as Concat().

[Anonymous]

Petter you're my hero!

Thanks everyone for all the answer, i now know about generic tables and i've my solution!

Have a great day!