Group by

[Anonymous]

Hi

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)

[vishsaggi]

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?

[Anonymous]

Hi Vishwarath

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

Kind regards

Nayan

[vishsaggi · Accepted solution]

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;

[Anonymous]

Hi

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

[antoniotiman]

Hi,

see Attachment.

Regards,

Antonio

[Anonymous]

Hi Antonio

Thank you for your reply.  Much appreciated.

Kind regards

Nayan