Optimized Script

Tue, 29 Mar 2011 17:31:37 GMT

Hi,

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_LAB:
LOAD
KEY AS KEY_LAB,
REF_ID AS REF_ID_LAB
RESIDENT Original_Source
where
SOURCE = 'LAB'
AND EXISTS (REF_ID_DUPLICATES, 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;

/***********************************************************/

topic Optimized Script in QlikView

Optimized Script