topic Re: Need help for group by in QlikView

Need help for group by

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

Hi,

I am trying to load the four different excel files like below.

Table1:

ID,

Manager

Table2:

Status

Table3:

Status

Table 4:

Status

So i need to Group by ID,Manager and Max(Status) . So how can i achieve this?

Re: Need help for group by

vishsaggi — Fri, 11 May 2018 17:47:00 GMT

Table2, 3, 4 do they have same name and number of columns in them? If yes autoconcatenation happens and you will left with Table2 with concatenated values from table3 and table4. Now, what is the link between Table1 and other tables, i mean any other columns you have in these that can join with Table1 or they going to be a datalsland?

Re: Need help for group by

eduardo_dimperio — Fri, 11 May 2018 17:49:41 GMT

Well, but how you want to agroup ID,Manager and Status if they dont have common fields?

Re: Need help for group by

pinky1234 — Fri, 11 May 2018 17:54:25 GMT

Table1:

ID,

Manager

Table2:

ID,

Status

Table3:

ID,

Status

Table4:

ID,

Status

From all the four tables id is the common one. I need to Group by ID,Manager and Max(Status).

Re: Need help for group by

pinky1234 — Fri, 11 May 2018 17:54:56 GMT

Can you post the syntax?

Re: Need help for group by

pinky1234 — Fri, 11 May 2018 17:55:27 GMT

Table1:

ID,

Manager

Table2:

ID,

Status

Table3:

ID,

Status

Table4:

ID,

Status

From all the four tables id is the common one. I need to Group by ID,Manager and Max(Status).Can you post the syntax please?

Re: Need help for group by

pinky1234 — Fri, 11 May 2018 17:58:23 GMT

Table2,table3 and table4 is the same number of columns with the same name. All four tables will have common name which is ID.

Re: Need help for group by

vishsaggi — Fri, 11 May 2018 18:14:41 GMT

May be try this? Change excel source name as yours.

Table2:

LOAD ID, Status

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet3);

Table3:

LOAD ID, Status

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet4);

Table4:

LOAD ID, Status

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet5);

LEFT JOIN(Table2)

Table1:

LOAD ID,

Manager

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet2);

LEFT JOIN(Table2)

LOAD ID, Manager, Max(Status) AS MaxStatus

Resident Table2

Group By ID, Manager;

Re: Need help for group by

eduardo_dimperio — Fri, 11 May 2018 19:01:51 GMT

Ahhhh now you can in two diferent way.

Table1:

LOAD

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet2);

LEFT JOIN (TABLE1)

Table2:

LOAD

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet3);

LEFT JOIN (TABLE1)

Table3:

LOAD

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet4);

LEFT JOIN (TABLE1)

Table4:

LOAD ID, Status

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet5);

Table_Result:

LOAD

ID,

Max(Status) AS MAX_STATUS,

Manager

RESIDENT Table1

GROUP BY

Manager,

ID;

//*************************************************************************

Table2:

LOAD

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet3);

CONCATENATE (TABLE2)

Table3:

LOAD

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet4);

CONCATENATE (TABLE2)

Table4:

LOAD ID, Status

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet5);

LEFT JOIN (TABLE2)

Table1:

LOAD

FROM

[Forecast_sampledata.xlsx]

(ooxml, embedded labels, table is Sheet2);

Table_Result:

LOAD

ID,

Max(Status) AS MAX_STATUS,

Manager

RESIDENT Table2

GROUP BY

Manager,

ID;

Re: Need help for group by

pinky1234 — Fri, 11 May 2018 19:31:49 GMT

Everything is working. When i try to show that in table box Id,Manager and Status. I don't see the status column anything means showing column is empty

Re: Need help for group by

pinky1234 — Fri, 11 May 2018 19:57:35 GMT

I am posting my script here can i know where am i going wrong here please

Table2:

LOAD [First Name],

M.I.,

[Last Name],

Suffix,

[External ID],

Attempt,

[Attempt Time],

[Delivery Method],

Confirmed,

[Confirmation Time],

[Call Result],

[Everbridge Mobile Safety App Comments],

Location,

Image,

[Notification ID]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate(Table2)

Table3:

LOAD [First Name],

M.I.,

[Last Name],

Suffix,

[External ID],

Attempt,

[Attempt Time],

[Delivery Method],

Confirmed,

[Confirmation Time],

[Call Result],

[Everbridge Mobile Safety App Comments],

Location,

Image,

[Notification ID]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate(Table2)

Table4:

LOAD [First Name],

M.I.,

[Last Name],

Suffix,

[External ID],

Attempt,

[Attempt Time],

[Delivery Method],

Confirmed,

[Confirmation Time],

[Call Result],

[Everbridge Mobile Safety App Comments],

Location,

Image,

[Notification ID]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LEFT JOIN(Table2)

Table1:

LOAD [Contact First Name],

[Contact Last Name],

[Contact External Id] as [External ID],

Emp1,

Emp2,

Emp3,

Emp4,

Emp5,

Emp6,

Emp7,

Emp8,

Emp9,

Emp10,

Emp11,

Emp12,

Emp13,

Emp14,

Emp15

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Table_Result:

LOAD [External ID],

Max(Confirmed) as Max_Status,

Emp2

Resident Table2

Group By [External ID],Emp2;

Re: Need help for group by

vishsaggi — Fri, 11 May 2018 20:30:01 GMT

You missed a LeftJoin before Table_Result.

LEFT JOIN(Table2)

Table_Result:

LOAD [External ID],

Max(Confirmed) as Max_Status,

Emp2

Resident Table2

Group By [External ID],Emp2;