Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;