Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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
Partner
Partner

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

Partner
Partner

Please refer to this :

The Generic Load

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

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 ?

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 ?

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

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!