Qlik Community

New to Qlik Sense

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

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Theo_Westseit
Contributor III
Contributor III

Group by Date and Name doesnt work (probably easy mistake....)

Hello guys !

Need urgently your help with the Group function....

This is how the table (data source) looks like:

NameDateHoursHours-Category
John Doe01.01.011,5A
John Doe01.01.012,0B
John Doe01.02.011,5A
John Doe01.02.012,3C

 

This is how the result of the ETL should look like (it needs to be grouped by Name and Date)

NameDateABC
John Doe01.01.011,52,0 
John Doe01.02.011,5 2,3

 

My ETL Script creates for each Hours-Category one column (which is good) but , it doesnt aggregate Name and Date into one row. Instead the result looks like this:

NameDateABC
John Doe01.01.011,5  
John Doe01.01.01 2,0 
John Doe01.02.011,5  
John Doe01.02.01  2,3

 

Heres comes my ETL-Script....where am i mistaken?? (FieldD contains Hours)

 

Report_Temp:
LOAD

FieldA As Name,
FieldB As Date,
FieldC As Hours-Category,
If(FieldC=’A’, num(sum(FieldD),'#.##0,0')) As A,
If(FieldC='B', num(sum(FieldD),'#.##0,0')) As B,
If FieldC='C', num(sum(FieldD),'#.##0,0')) As C

FROM [lib://DATA_Public/EXPORT.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq) Group by FieldA, FieldB, FieldC;

Report:
Name,
Date,
A,
B,
C

Resident Report_Temp;
Drop Table Report_Temp;

 

Thanks 4 ure help!!!

Labels (1)
1 Solution

Accepted Solutions
kaushiknsolanki

Try this.

Report_Temp:
LOAD
FieldA As Name,
FieldB As Date,
FieldC As Hours-Category,
If(FieldC=’A’,FieldD) As A,
If(FieldC='B',FieldD) As B,
If FieldC='C',FieldD) As C,

FROM [lib://DATA_Public/EXPORT.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq);

Report:
Load Name,Date,Sum(A) as A,Sum(B) as B, Sum(C) as C
Resident Report_Temp
Group by Name,Date;

Drop table Report_Temp;

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

5 Replies
chrismarlow
Specialist II
Specialist II

Hi,

I think your IF needs to be inside your sum, so try;

Sum(If(FieldC=’A’, FieldD,Null())) As A,

etc.

Cheers,

Chris.

 

Theo_Westseit
Contributor III
Contributor III
Author

Thanks for the reply chrismarlow! ....Unfortunately, the result looks exactly like mine...

Any furhter ideas?...

chrismarlow
Specialist II
Specialist II

Hi,

Hmmmm.

Think you need to not include by FieldC/HoursCategory in your load with the group by.

Cheers,

Chris.

kaushiknsolanki

Try this.

Report_Temp:
LOAD
FieldA As Name,
FieldB As Date,
FieldC As Hours-Category,
If(FieldC=’A’,FieldD) As A,
If(FieldC='B',FieldD) As B,
If FieldC='C',FieldD) As C,

FROM [lib://DATA_Public/EXPORT.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq);

Report:
Load Name,Date,Sum(A) as A,Sum(B) as B, Sum(C) as C
Resident Report_Temp
Group by Name,Date;

Drop table Report_Temp;

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Theo_Westseit
Contributor III
Contributor III
Author

You R just incredible !!!! Thanks a lot. It works perfect 🙂