Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cliff_clayman
Creator II
Creator II

How can I do something similar to Aggr() in script to group by a field for a Sum?

I am loading a data table with multiple fields. I need to do a group by for a sum on just one of those fields. I currently have it to where I get two tables, but I just want one table in the end. Here is what I currently have:

 

Table1:
LOAD [Company],
[Account],
[Document],
[Year],
[Date],
[Amount],
[Type],
[Center]
FROM
C:\File.xlsx
(ooxml, embedded labels, table is Sheet1);


LOAD
[Document],
If(Sum([Amount])<>0,'Open','Closed') as [Status]
Resident Table1
GROUP BY
[Document];

Is there another way I can do this so I can have just one table and all my fields as well?

1 Solution

Accepted Solutions
Taoufiq_Zarra

Maye be :

Table1:
LOAD [Company],
[Account],
[Document],
[Year],
[Date],
[Amount],
[Type],
[Center]
FROM
C:\File.xlsx
(ooxml, embedded labels, table is Sheet1);

left join

LOAD
[Document],
If(Sum([Amount])<>0,'Open','Closed') as [Status]
Resident Table1
GROUP BY
[Document];
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

1 Reply
Taoufiq_Zarra

Maye be :

Table1:
LOAD [Company],
[Account],
[Document],
[Year],
[Date],
[Amount],
[Type],
[Center]
FROM
C:\File.xlsx
(ooxml, embedded labels, table is Sheet1);

left join

LOAD
[Document],
If(Sum([Amount])<>0,'Open','Closed') as [Status]
Resident Table1
GROUP BY
[Document];
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉