Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

Logic help

Hi Experts,

Can any one please help me on below requirement.

I have three levels like H,L,M in Level column,

first priority is H, Second Priority is L,Third priority is M.

1.If any employee is in Three Levels for example in the below sample data KELLY is in 3 levels but need to put him in only Level=H

and remaining two rows need to remove in the table.

2. JOHN is in L,M two levels L is the high priority compared to M so need to keep only L row and remove M row of JOHN

3.GOW is in only one level i.e., M so need to keep as it is.

The above fields contains the sample data like below. All these fields are in same table in the backend.

Original data:

Name        Level         Sales      DESIGNATION          Created              Status

KELLY      H               5263       MANAGER                25/07/2018

KELLY      L                5123       MANAGER                                            Active

KELLY      M               8690       ASS MANAGER        30/05/2018

JOHN       L                 5699       MANAGER

JOHN       M                583        ASS MANAGER                                    Active

GOW        M                5869       MANAGER 



Expected data Output:

Name        Level         Sales      DESIGNATION          Created              Status

KELLY      H               5263       MANAGER                25/07/2018

JOHN       L                 5699       MANAGER

GOW        M                5869       MANAGER 

Please help me on this.

Thanks in advance

1 Solution

Accepted Solutions

Re: Logic help

Try this

Table:

LOAD *,

Match(Level, 'M', 'L', 'H') as LevelNum;

LOAD * INLINE [

    Name, Level, Sales, DESIGNATION, Created, Status

    KELLY, H, 5263, MANAGER, 25/07/2018

    KELLY, L, 5123, MANAGER, , Active

    KELLY, M, 8690, ASS MANAGER, 30/05/2018

    JOHN, L, 5699, MANAGER

    JOHN, M, 583, ASS MANAGER, , Active

    GOW, M, 5869, MANAGER

];


Right Join (Table)

LOAD Name,

Max(LevelNum) as LevelNum

Resident Table

Group By Name;

2 Replies

Re: Logic help

Try this

Table:

LOAD *,

Match(Level, 'M', 'L', 'H') as LevelNum;

LOAD * INLINE [

    Name, Level, Sales, DESIGNATION, Created, Status

    KELLY, H, 5263, MANAGER, 25/07/2018

    KELLY, L, 5123, MANAGER, , Active

    KELLY, M, 8690, ASS MANAGER, 30/05/2018

    JOHN, L, 5699, MANAGER

    JOHN, M, 583, ASS MANAGER, , Active

    GOW, M, 5869, MANAGER

];


Right Join (Table)

LOAD Name,

Max(LevelNum) as LevelNum

Resident Table

Group By Name;

mahitham
Contributor

Re: Logic help

Thanks for your help sunny.

Community Browser