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

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
Highlighted
MVP
MVP

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
Highlighted
Partner
Partner

Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

Please refer to this :

The Generic Load

Highlighted
Partner
Partner

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

Highlighted
Contributor III
Contributor III

Hey Omar,

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

Highlighted
Contributor III
Contributor III

Hey Arthur,

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

Highlighted
MVP
MVP

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

Highlighted
Contributor III
Contributor III

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!