Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
vincent_bellang
New 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
MVP
MVP

Re: Group by explanation

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

Re: Group by explanation

Partner
Partner

Re: Group by explanation

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

Partner
Partner

Re: Group by explanation

Please refer to this :

The Generic Load

Partner
Partner

Re: Group by explanation

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
New Contributor III

Re: Group by explanation

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
New Contributor III

Re: Group by explanation

Hey Arthur,

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

MVP
MVP

Re: Group by explanation

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

vincent_bellang
New Contributor III

Re: Group by explanation

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!