Creating Dummy Variables and Filtering Data in Qlik Sense’s Data Loading Script

[Newbie_QLIK]

Hi Qlik Sense community,

I hope this finds you well.

I’m working on creating four dummy variables to filter my final dataset. However, when I load the script, I only see TempTable and not FilteredTable. If I drop TempTable (using DROP TABLE TempTable;), I end up with no tables. I suspect this might be because my dataset only contains rows where (duplicates_DIV_ID_identique_var_nul = 0 OR duplicates_DIV_ID_different_var_identique = 0). Can anyone explain why FilteredTable is not appearing?

Here are the steps :

1. Load variables from Oracle.
2. Create duplicate variables based on U_ID & DIV_ID & Year(DT_EMISSION) and U_ID & VARIATION & Year(DT_EMISSION).
3. Create two variables for the duplicate filter:
   • Keep duplicates with U_ID & DIV_ID & Year(DT_EMISSION) where (IsNull(VARIATION) OR VARIATION = 0).
   • Keep duplicates with U_ID & VARIATION & Year(DT_EMISSION) where DIV_ID = 1.
4. Exclude rows that fit the above conditions.

Here’s my script:

LIB CONNECT TO 'XXXXXX';
YourTableName:
LOAD "T_ID",
"U_ID",
"year_role",
"DIV_ID",
"CD_IMM",
VARIATION,
"DT_EMISSION",
Year("DT_EMISSION") as Year_DT_EMISSION,
IF(Exists("U_ID" & "DIV_ID" & Year("DT_EMISSION")), 1, 0) as duplicates_DIV_ID_same,
IF(Exists("U_ID" & "VARIATION" & Year("DT_EMISSION")), 1, 0) as duplicates_var_same,
;

SQL SELECT "T_ID",
"U_ID",
"year_role",
"DIV_ID",
"CD_IMM",
VARIATION,
"DT_EMISSION",
FROM XXX."XXXXXXX"
WHERE "CD_IMM" = 'I'
AND "DT_EMISSION" >= '2020-01-01'
AND "year_role" = '2022';

TempTable:
LOAD *,
IF(duplicates_DIV_ID_same= 1 AND (IsNull("VARIATION") OR "VARIATION" = 0), 1, 0) as duplicates_DIV_ID_same_var_nul,
IF(duplicates_var_same = 1 AND "DIV_ID" = 1, 1, 0) as duplicates_DIV_ID_different_var_same
RESIDENT YourTableName;

DROP TABLE YourTableName;

FilteredTable:
LOAD *
RESIDENT TempTable
WHERE NOT (duplicates_DIV_ID_same_var_nul = 1 OR duplicates_DIV_ID_different_var_same= 1);

TRACE 'FilteredTable created successfully';
TRACE 'Number of rows in FilteredTable: ' & NoOfRows('FilteredTable');

+ I've also discovered that my codes below do not detect duplicates based on the three columns (i.e,:  IF(Exists("UEF_ID" & "DIV_ID" & "DT_EMISSION"), 1, 0) as doublons_DIV_ID_identique,
IF(Exists("UEF_ID" & "VARIATION" & "DT_EMISSION"), 1, 0) as doublons_var_identique; ) Does anyone know what is the right code to identify the duplicates?

Thank you for reading my post. Hope my question is clear.

Best,

[rwunderlich · Accepted solution]

What you are seeing is automatic concatenation. The FilteredTable load is appending to the TempTable. Add "NoConcatenate"

FilteredTable:
NoConcatenate LOAD *
RESIDENT TempTable
WHERE NOT (duplicates_DIV_ID_same_var_nul = 1 OR duplicates_DIV_ID_different_var_same= 1);

-Rob

[Newbie_QLIK]

Hi Rob,

Thank you for the quick reply! The filteredTable is now visible!

Best,