topic Re: sum() aggr() group by... in App Development

sum() aggr() group by...

xyz_1011 — Tue, 05 Mar 2024 14:38:39 GMT

Hi all,

its probably quite obvious, but i am fiddling around with the below for some time now and cant get it to work. I have the following (excerpt of my) data set:

Batch_ID	Item	Weight	Referral	Relevance
XX	A	16,00%	100,00%	16,00%
XX	B	11,00%	0,00%	0,00%
XX	C	17,00%	40,00%	6,80%
XX	D	2,00%	100,00%	2,00%
XX	E	6,00%	100,00%	6,00%
XX	F	12,00%	100,00%	12,00%
XX	G	2,00%	0,00%	0,00%
XX	H	25,50%	40,00%	10,20%
XX	I	8,50%	25,00%	2,13%

				55,13%

I am trying to load (in my script) the data above and have it grouped by Batch_ID. For some reason i never get to the 55.13% for the given Batch_ID above (which i have sone in XLS by summing up the column "Relevance") - Any hint would be much appreciated.

Thanks!!

Re: sum() aggr() group by...

marcus_sommer — Tue, 05 Mar 2024 14:55:07 GMT

Add a recno() and rowno() to the load and pull these information with the other fields into a table-box to have a look on the really loaded data.

Re: sum() aggr() group by...

LRuCelver — Wed, 06 Mar 2024 07:30:38 GMT

Data: NoConcatenate Load *, Weight * Referral as Relevance Inline [ Batch_ID, Item, Weight, Referral XX, A, 0.16, 1 XX, B, 0.11, 0 XX, C, 0.17, 0.4 XX, D, 0.02, 1 XX, E, 0.06, 1 XX, F, 0.12, 1 XX, G, 0.02, 0 XX, H, 0.255, 0.4 XX, I, 0.085, 0.25 ]; Batch_Relevance: NoConcatenate Load Batch_ID, Sum(Relevance) as Batch_Relevance Resident Data Group By Batch_ID;

Re: sum() aggr() group by...

ali_hijazi — Wed, 06 Mar 2024 07:30:49 GMT

Qlik shows on a table the distinct values
so if you have two rows in the source data with same values then Qlik on a table displays one of them
to make sure that this is what is happening, you need to add a new column to your data as follows:
Load *, rowno() as unique_id...
add this column (unique_id) to your table and you must see duplicate rows except for the unique id you added

Otherwise you need to share sample data / qvf file to do more investigations

Re: sum() aggr() group by...

Dataintellinalytics — Sat, 16 Mar 2024 07:30:06 GMT

Please share the raw data along with formula used in both Excel and Qlik