Group by with a Logic

[chriys1337]

Hi,

I am having troubles to realize following logic.

Per "ID" there can be in the field "Role_Desc" either a 'Competitor', a 'Competitor (Marketing)' and / or a 'Shipper'.

I would like to create a single field with the logic:

If there is a 'Competitor(Marketing)' in the field "Role_Desc", than display the corresponding "Short_Name",

if not, than display the 'Competitor'

if not, than display the 'Shipper'.

If there is more than one "Short_Name" per "Role_Desc", than these entries should be concatenated.

The final result should be a table where I get per ID just one Short_Name field.

I am grateful for any help!!

Thx

Chris !

[sunny_talwar · Accepted solution]

May be try this

one:

LOAD *,

Match(Role_Desc, 'Competitor (Marketing)', 'Competitor', 'Shipper') as Role_Desc_Rank;

LOAD * INLINE [

    ID, Role_Desc, Short_Name

    1, Shipper, COSCO

    1, Competitor, DHL

    2, Competitor, Lloyd

    2, Competitor, THAI Airways

    3, Shipper, UASC

    4, Shipper, Lufthansa

    4, Competitor, Eurowings

    4, Competitor (Marketing), HANSA AG

];

Right Join (one)

LOAD ID,

Min(Role_Desc_Rank) as Role_Desc_Rank

Resident one

Group By ID;

two:

LOAD ID,

Concat(Short_Name, ', ') as Short_Name

Resident one

Group By ID;

DROP Table one;

[chriys1337]

That works, thank you very much Sunny.