Need help for group by

[pinky1234]

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?

[vishsaggi]

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?

[eduardo_dimperio]

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

[pinky1234]

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

[pinky1234]

Can you post the syntax?

[pinky1234]

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?

[pinky1234]

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

[vishsaggi]

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;

[eduardo_dimperio · Accepted solution]

Ahhhh now you can in two diferent way.

1)

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;

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

2)

2)

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;

[pinky1234]

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