Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
chriys1337
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
sunny_talwar

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
sunny_talwar

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

chriys1337
Creator II
Creator II
Author

That works, thank you very much Sunny.