I was looking for an optimized statement (If there is any) for the below script/logic. I am able to get the desired results, but just want to know, if there is any simpler function in qlikView to get the same result.
Condition:
· Loop through the records and if duplicate exists
· If there is one with Source = LAB, select
· ElseIf Source = LAS
· ElseIf Source = LAD
· ElseIf Source = LAT
Script:
Original_Source: LOAD * INLINE [ REF_ID, KEY, SOURCE 10, 1, LAB 10, 2, LAS 10, 3, LAD 10, 4, LAT 20, 5, LAS 20, 6, LAD 20, 7, LAT 30, 8, LAD 30, 9, LAT 40, 10, LAT 50, 11, LAB 60, 12, LAS 70, 13, LAD 80, 14, LAT ];
REF_Duplicates: LOAD REF_ID AS REF_ID_DUPLICATES WHERE REF_ID_DUPLICATE>1; LOAD REF_ID, Count(REF_ID) AS REF_ID_DUPLICATE RESIDENT Original_Source GROUP BY REF_ID;
REF_LAS: LOAD KEY AS KEY_LAS, REF_ID AS REF_ID_LAS RESIDENT Original_Source where SOURCE = 'LAS' AND EXISTS (REF_ID_DUPLICATES, REF_ID) AND NOT EXISTS (REF_ID_LAB, REF_ID);
REF_LAD: LOAD KEY AS KEY_LAD, REF_ID AS REF_ID_LAD RESIDENT Original_Source where SOURCE = 'LAD' AND EXISTS (REF_ID_DUPLICATES, REF_ID) AND NOT EXISTS (REF_ID_LAB, REF_ID) AND NOT EXISTS (REF_ID_LAS, REF_ID); /***********************************************************/
REF_LAT: LOAD KEY AS KEY_LAT, REF_ID AS REF_ID_LAT RESIDENT Original_Source where SOURCE = 'LAT' AND EXISTS (REF_ID_DUPLICATES, REF_ID) AND NOT EXISTS (REF_ID_LAB, REF_ID) AND NOT EXISTS (REF_ID_LAS, REF_ID) AND NOT EXISTS (REF_ID_LAD, REF_ID);
/***********************************************************/ Temp: Load KEY_LAB as KEY_Temp, REF_ID_LAB AS REF_ID_Temp Resident REF_LAB; concatenate Load KEY_LAD as KEY_Temp, REF_ID_LAD AS REF_ID_Temp Resident REF_LAD; concatenate Load KEY_LAS as KEY_Temp, REF_ID_LAS AS REF_ID_Temp Resident REF_LAS; concatenate Load KEY_LAT as KEY_Temp, REF_ID_LAT AS REF_ID_Temp Resident REF_LAT;
Noconcatenate Final: Load KEY, REF_ID Resident Original_Source where not exists(REF_ID_Temp, REF_ID) ; concatenate Load KEY_Temp as KEY, REF_ID_Temp as REF_ID Resident Temp; DROP Table Original_Source, Temp, REF_LAB, REF_LAD, REF_LAS, REF_LAT;
