topic Re: Group by in QlikView

Group by

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I need assistance with "Group by". I want to apply Group By to the script below and a sum aggregation should be on Total_Doors_3. Also, I want the field "Outlet" to be dropped.

After reload, i still want the same values in the chart table that i have on the front end.

Currently chart table I have a sum aggregation based on Outlet.

Attached is the Qlikview Model and the source data.

Thank you

Kind regards

Nayan

LOAD [Calendar Year/Month],

Country,

Division,

Outlet,

Region,

if(len(Total_Doors_1) =0,0, Total_Doors_1) as Total_Doors_1,

if(len(Total_Doors_2) =0,0, Total_Doors_2) as Total_Doors_2,

Total_Doors_3

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq)

Re: Group by

vishsaggi — Sun, 18 Jun 2017 14:53:42 GMT

You have two tables in the front end? Which one and your expressions for Sum () is not using any aggr() on your outlet? Can you tell me which table you are looking for expected output the big one or small table?

Re: Group by

Anonymous — Sun, 18 Jun 2017 15:18:42 GMT

Hi Vishwarath

The big table. However both tables has the same value.

Kind regards

Nayan

Re: Group by

vishsaggi — Sun, 18 Jun 2017 15:44:06 GMT

Can you elaborate a little please? IS your total_doors_3 field should be grouped by Outlet, but you dont want the outlet to be loaded. And can you let me know what values should total_doors_3 should hold the values based on sum?

I tried this just check?

TABLE1:

LOAD [Calendar Year/Month],

Country,

Division,

Outlet,

Region,

if(len(Total_Doors_1) =0,0, Total_Doors_1) as Total_Doors_1,

if(len(Total_Doors_2) =0,0, Total_Doors_2) as Total_Doors_2,

Total_Doors_3

FROM

C:\Users\VNAGARAJ\Desktop\Data.txt

(txt, unicode, embedded labels, delimiter is '\t', msq);

LEFT JOIN (TABLE1)

TABLE1:

LOAD Outlet,

Sum(Total_Doors_3) AS Total_Doors_3Sum

FROM C:\Users\VNAGARAJ\Desktop\Data.txt

(txt, unicode, embedded labels, delimiter is '\t', msq)

GROUP BY Outlet;

DROP FIELD Outlet;

Re: Group by

Anonymous — Sun, 18 Jun 2017 15:55:41 GMT

Thank you for your reply. Yes, that is correct. The value is:

if Total Doors 1 + Total Doors 2 = 1 , sum (Total Doors 2 )

This is just an extract of the model i am working with. I have many fact tables that are concatenated to each other. And all these do not have Outlet as a field. This table which has outlet in it, does work, however it makes my Qlikview model very slow to work with on the front end.

Hence thats why i want to aggregate (ie Group by) in the backend.

Hope this makes sense.

Kind regards

Nayan

Re: Group by

antoniotiman — Sun, 18 Jun 2017 16:05:27 GMT

Hi,

see Attachment.

Regards,

Antonio

Re: Group by

Anonymous — Sun, 18 Jun 2017 16:13:36 GMT

Hi Antonio

Thank you for your reply. Much appreciated.

Kind regards

Nayan