Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
rmendoza
Contributor II
Contributor II

Sum and Group

Hello dear community

I have a problem:

I have a QVD and excel with the following data:

I need to find the Excel ID in the QVD group.

A group can have several IDs but I only need the ID that is in the excel and the total sum of all the IDs in the group.

rmendoza_0-1718197988536.png

Thanks for your help

Cheers

RM

 

 

Labels (3)
1 Solution

Accepted Solutions
maxgro
MVP
MVP

Maybe
 
// load from excel
T_Excel:
LOAD ID 
FROM File_Excel.txt (txt, utf8, embedded labels, delimiter is ';', msq);
 
// load distinct group from qvd, only ID in excel 
T_Groups:
LOAD distinct 
     Group_ID
FROM File_QVD.qvd (qvd)
WHERE EXISTS (ID);
 
// map from group_ID to ID, only ID in excel
MapGroupToID:
MAPPING LOAD Group_ID, ID
FROM File_QVD.qvd (qvd)
WHERE EXISTS (ID);
 
// load the final table
T_Result:
LOAD
ApplyMap('MapGroupToID', Group_ID) as ID,
SUM(Value) AS Value
FROM File_QVD.qvd (qvd)
WHERE EXISTS (Group_ID)
GROUP BY ApplyMap('MapGroupToID', Group_ID);

 

I think the result for ID 1 is 5390, not 5393

For Qlik Sense (I used QlikView) adapt the loads

maxgro_0-1718204227485.png

 

View solution in original post

5 Replies
Qlik_Nir
Contributor
Contributor

Can you elaborate the Question ?

Qlik_Nir
Contributor
Contributor

Is this what you are expecting? 

Qlik_Nir_0-1718203373033.png

 

maxgro
MVP
MVP

Maybe
 
// load from excel
T_Excel:
LOAD ID 
FROM File_Excel.txt (txt, utf8, embedded labels, delimiter is ';', msq);
 
// load distinct group from qvd, only ID in excel 
T_Groups:
LOAD distinct 
     Group_ID
FROM File_QVD.qvd (qvd)
WHERE EXISTS (ID);
 
// map from group_ID to ID, only ID in excel
MapGroupToID:
MAPPING LOAD Group_ID, ID
FROM File_QVD.qvd (qvd)
WHERE EXISTS (ID);
 
// load the final table
T_Result:
LOAD
ApplyMap('MapGroupToID', Group_ID) as ID,
SUM(Value) AS Value
FROM File_QVD.qvd (qvd)
WHERE EXISTS (Group_ID)
GROUP BY ApplyMap('MapGroupToID', Group_ID);

 

I think the result for ID 1 is 5390, not 5393

For Qlik Sense (I used QlikView) adapt the loads

maxgro_0-1718204227485.png

 

rmendoza
Contributor II
Contributor II
Author

Hello @maxgro 

Thank you very much for your help.

You have helped me a lot.

How can I add the "Group" field? in the final table.

Thx.

RM

maxgro
MVP
MVP

add an aggregation function (only, maxstring, minstring, ...)

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/Aggrega...

 

// load the final table
T_Result:
LOAD
ApplyMap('MapGroupToID', Group_ID) as ID,
SUM(Value) AS Value,
ONLY(Group_ID) AS Group_ID
FROM File_QVD.qvd (qvd)
WHERE EXISTS (Group_ID)
GROUP BY ApplyMap('MapGroupToID', Group_ID)
;