Discussion Board for collaboration on QlikView Scripting.
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!!
May be try this
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)
Min(Role_Desc_Rank) as Role_Desc_Rank
Group By ID;
Concat(Short_Name, ', ') as Short_Name
DROP Table one;
View solution in original post
That works, thank you very much Sunny.