Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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;
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;
That works, thank you very much Sunny.