Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vincent_bellang
Contributor III
Contributor III

Group by explanation

Greetings,

I've a table like this

School:

NameClassTeacher
DiazEnglishSantiago
DiazFrenchTerry
DiazGermanScully
HoltEnglishSantiago
HoltFrenchTerry
HoltGermanScully
PeraltaEnglishSantiago
PeraltaFrenchHitchcock
PeraltaGermanScully

And i would like to change it to that way:

Final:

NameEnglishFrenchGerman
DiazSantiagoTerryScully
HoltSantiagoTerryScully
PeraltaSantiagoHitchcockScully

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:

NameEnglishFrenchGerman
Diaz---
DiazSantiago--
Diaz-Terry-
Diaz--Scully
Holt---
HoltSantiago--
Holt-Terry-
Holt--Scully
Peralta---
PeraltaSantiago--
Peralta-Terry-
Peralta--Scully

I tried group by but i need an agregation function.

Thanks for the answers!

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

8 Replies
sewialwork
Partner - Contributor III
Partner - Contributor III

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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
Partner - Specialist III
Partner - Specialist III

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

vincent_bellang
Contributor III
Contributor III
Author

Hey Omar,

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

vincent_bellang
Contributor III
Contributor III
Author

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
Partner - Champion III
Partner - Champion III

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

vincent_bellang
Contributor III
Contributor III
Author

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!