filter lines by lookup value of other column

[DirkPuylaert]

Hello, 

I'm new to qliksense an would appreciate assistance.

I need a report where all records are showing based on a lookup of a value in other record.

example xls as attachchement

filter value in record MC_Code (where value is "CR0101")

Show all lines whith same "event-ID" 

thanks in advance,

Dirk

[rpennacchi]

Hello, Dirkpuylaert

Paste this script on your Qlik Sense Script Editor. I think it will help you out:

// Loading data

Event_Data:
LOAD * INLINE [
reference , date - time , event-ID , MC_Code , MC_Tekst
88888 , 06/03/2023 14:44:07 , 23030601279 , $ACCEPT , text 1
88888 , 06/03/2023 14:44:07 , 23030601279 , $VALIDATE , text 2
88888 , 06/03/2023 14:44:07 , 23030601279 , CR0101 , text 3
88888 , 06/03/2023 14:44:07 , 23030601279 , MSG , text 4
88888 , 06/03/2023 14:44:07 , 23030601279 , PR001 , text 5
88888 , 06/03/2023 14:44:07 , 23030601279 , PR002 , text 6
88888 , 06/03/2023 14:49:59 , , MSG , text 7
88888 , 06/03/2023 14:50:47 , , PR003 , text 8
88888 , 06/03/2023 14:49:24 , , PR004 , text 9
88888 , - - , , PR005 , text 10
];

// This will create a look up based on "event-ID" as a search parameter and MC_Code as a returning parameter

Event_Has_MC_Code_CR0101:
MAPPING LOAD DISTINCT
"event-ID",
MC_Code
RESIDENT Event_Data
WHERE MC_Code = 'CR0101';

// This will filter each "event-ID" which was found on 'Event_Has_MC_Code_CR0101'

TMP_Event_Data:
NOCONCATENATE LOAD
*
RESIDENT Event_Data
WHERE NOT ISNULL(APPLYMAP('Event_Has_MC_Code_CR0101', "event-ID", NULL()));

// This will delete the original table and rename the temporary table to it's original name

DROP TABLE Event_Data;
RENAME TABLE TMP_Event_Data TO Event_Data;