Additional RECORDS Dropped on RIGHT JOIN - PLEASE HELP!!

[#1]

Guys, please help me fix this issue..

Requirement: I am checking for particular region ids in the existing QVD. If it exists then i am dropping the MATCHING set of REGION ID's from the existing QVD and writing the REMAINING/REGION Id's Records to the EXISITING QVD AGAIN

Below are the steps followed :

1)      Read the DELTA FILE input data

2)      RESIDENT Load the the contents of the input DELTA file to TEMP table (Qlikview) and sort by REGION ID

3)      RIGHT JOIN the above TEMP table with EXISTING QVD data (read from destination folder) USING THE KEY REGION_ID and remove the REGION IDs from EXISTING QVD for the region ids that are already existing in TEMP TABLE.

4)      Resultant TMP TABLE contains the REGIONID with single LOCATION ID's and the Remaining NBN LOCATION id mapped to the REGION ID's are getting DROPPED. I DO NOT want the
other LOCATION id's to drop.

ie,  If there are multiple instances of LOCATION id mapped to REGION ID, only one instance of a REGION ID and LOCATION ID is captured. I need other instances of LOCATION ID mapped to REGION's also to be captured.

I am facing issue on step 4. I have provided the actual / expected result below. I have actually coded the scrip based on the above steps but it is not working. PLEASE PROVIDE ME THE QV Script.

Provided the INPUT DELTA FILE CONTENT and EXISTING QVD CONTENT. (as to how it appears)

(Note: REGION ID is NOT UNIQUE but LOCATION ID is UNIQUE)

DELTA FILE CONTENT:

REGIONID LOCATION ID     

2BVM-01  111
2BVM-01  222

EXISTING QVD:

REGIONID LOCATION ID

2BVM-01      111
2BVM-01       222
2BVM-01       888
2BVM-01       999
2BVM-02       777
2BVM-02       666
2BVM-03       555
2BVM-03       444

ACTUAL RESULT

2BVM-02  777
2BVM-03  555

EXPECTED RESULT

REGIONID LOCATION ID

2BVM-02       777
2BVM-02       666
2BVM-03       555
2BVM-03       444

If i am using LOCATION ID as a KEY for RIGHT JOIN below is the result

2BVM-01  888

2BVM-01  999

2BVM-02  777

2BVM-02  666

2BVM-03  555

2BVM-03  444

[jagan]

Hi,

I think you need to join the table by using REGION ID, can you attach the script, why can't you use the Exists() instead of joining the tables.  Can you attach the script you are using.

Regards,

Jagan.

[#3]

Hi Jagan,

Here is the code I am currently using which gives the above ACTUAL RESULT. I am using REGION_ID in WHERE NOT EXISTS(). Could you provide a sample code if you find any issues.

DELTA_FILE:

LOAD *
FROM ...\DELTA_REGION.dat

TEMP_DELTA:

LOAD REGION_ID, LOCATION_ID
RESIDENT TEMP_DELTA
ORDER BY REGION_ID;

RIGHT JOIN

LOAD *
FROM ...\EXISTINGQVD.qvd
WHERE NOT EXISTS (REGION_ID)

STORE TEMP_DELTA to EXISITINGQVD.qvd;

DROP TABLE TEMP_DELTA;

DROP TABLE DELTA_FILE;

[jagan]

Hi,

Try this

DELTA_FILE:

LOAD *
FROM ...\DELTA_REGION.dat;

CONCATENATE(DELTA_FILE)

LOAD *
FROM ...\EXISTINGQVD.qvd
WHERE NOT EXISTS (REGION_ID)

STORE DELTA_FILE to EXISITINGQVD.qvd;

DROP TABLE DELTA_FILE;

Regards,

Jagan.

[#5]

I am still getting the same result. LOCATION Id's 666 and 444 are getting dropped. Any other possible way??

ACTUAL RESULT

2BVM-02  777
2BVM-03  555

EXPECTED RESULT

2BVM-02  777
2BVM-02  666
2BVM-03  555
2BVM-03  444

[jagan]

Hi,

Try this

Data_Temp:

LOAD REGIONID, LOCATIONID
FROM ...\EXISTINGQVD.qvd
WHERE NOT EXISTS (REGION_ID);

LEFT JOIN(Data_Temp)

LOAD REGIONID, 1 AS Temp
FROM ...\DELTA_REGION.dat;

Data:

NoConcatenate

LOAD

REGIONID, LOCATIONID

RESIDENT Data_Temp

WHERE IsNull(Temp);

STORE Data_Temp to EXISITINGQVD.qvd;

DROP TABLE Data_Temp;

Regards,

Jagan.

[#7]

Hi Jagan,

Thx for your time. I am getting the below result this time.

2BVM-01  111

2BVM-02  777

2BVM-03  555

[jagan · Accepted solution]

Hi,

Try this script

Data_Temp:

LOAD REGIONID, LOCATIONID

FROM data.txt;

LEFT JOIN(Data_Temp)

LOAD REGIONID, 1 AS Temp

INLINE [

REGIONID, LOCATION ID    

2BVM-01,  111

2BVM-01,  222];

Data:

NoConcatenate

LOAD

REGIONID, LOCATIONID

RESIDENT Data_Temp

WHERE IsNull(Temp);

STORE Data into EXISITINGQVD.qvd;

DROP TABLE Data_Temp;

Regards,

Jagan.

[#9]

Hi,

I think keeping the REGION ID's in INLINE Table may not be a viable option. As these REGION ID's will be coming through the DELTA dat file and the data varies every week. NEW REGION ID's / Exising REGION ID's from EXISTING QVD may come through this DELTA file.

Do you still suggest to try the above given code?

Thanks

[jagan]

Hi,

Instead of INLINE you replace with your delta file.  Apart from that the script works fine?

Regards,

Jagan.