Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Group by with a Logic

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 !

1 Solution

Accepted Solutions
Highlighted

Re: Group by with a Logic

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;

View solution in original post

2 Replies
Highlighted

Re: Group by with a Logic

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;

View solution in original post

Highlighted
Creator II
Creator II

Re: Group by with a Logic

That works, thank you very much Sunny.