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

[Theo_Westseit]

Hello guys !

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

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

Name	Date	Hours	Hours-Category
John Doe	01.01.01	1,5	A
John Doe	01.01.01	2,0	B
John Doe	01.02.01	1,5	A
John Doe	01.02.01	2,3	C

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

Name	Date	A	B	C
John Doe	01.01.01	1,5	2,0
John Doe	01.02.01	1,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:

Name	Date	A	B	C
John Doe	01.01.01	1,5
John Doe	01.01.01		2,0
John Doe	01.02.01	1,5
John Doe	01.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!!!

[chrismarlow]

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]

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

Any furhter ideas?...

[chrismarlow]

Hi,

Hmmmm.

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

Cheers,

Chris.

[kaushiknsolanki · Accepted solution]

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;

[Theo_Westseit]

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