topic Re: Group by with a Logic in QlikView

Group by with a Logic

chriys1337 — Mon, 07 May 2018 14:53:55 GMT

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 — Mon, 07 May 2018 15:07:17 GMT

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 — Tue, 08 May 2018 11:31:04 GMT

That works, thank you very much Sunny.