Sum and Group

[rmendoza]

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.

Thanks for your help

Cheers

RM

[Qlik_Nir]

Can you elaborate the Question ?

[Qlik_Nir]

Is this what you are expecting? 

[maxgro · Accepted solution]

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

[rmendoza]

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]

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)
;