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?

[pinky1234]

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;

[vishsaggi]

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;