topic Outer with QVD files in QlikView

Outer with QVD files

Black_Hole — Sat, 16 Nov 2024 20:40:20 GMT

Hello All,

I would like to store in a table all the data from a QVD file which not exist in a table from another QVD file.

But after a lot of tests, I don't have the expected result.

Below, I put my current script:

TAB1_TEMP:
LOAD
AutoNumber(ID & NAME & DATE) AS %_KEY_XA,
*
FROM $(QVDPath)INCR1.qvd (QVD);

TAB2:
LOAD
AutoNumber(ID & NAME & DATE) AS %_KEY_XA,
*
FROM $(QVDPath)INCR2.qvd (QVD);

OUTER JOIN(TAB1_TEMP)

LOAD * RESIDENT TAB2;

NON_MATCH:
LOAD * RESIDENT TAB1_TEMP
WHERE ISNULL(ID);

TAB1:
LOAD * RESIDENT TAB1_TEMP
WHERE NOT ISNULL(ID);

Please could you tell me what is wrong in my script and how I can do in order to extract the data from TAB2 which not exist not in TAB1.

Thank you in advance for your help.

Re: Outer with QVD files

Claudiu_Anghelescu — Fri, 14 Jun 2019 21:03:48 GMT

TAB1_TEMP:
LOAD
AutoNumber(ID & NAME & DATE) AS %_KEY_XA,
*
FROM $(QVDPath)INCR1.qvd (QVD);

NoConcatenate;

TAB2:
LOAD
AutoNumber(ID & NAME & DATE) AS %_KEY_XA,
*
FROM $(QVDPath)INCR2.qvd (QVD);

OUTER JOIN(TAB1_TEMP)

LOAD * RESIDENT TAB2;

NoConcatenate;

NON_MATCH:
LOAD * RESIDENT TAB1_TEMP
WHERE ISNULL(ID);

NoConcatenate;

TAB1:
LOAD * RESIDENT TAB1_TEMP
WHERE NOT ISNULL(ID);

DROP TABLE TAB1_TEMP;

Re: Outer with QVD files

Vegar — Fri, 14 Jun 2019 21:20:44 GMT

As might notice that @Claudiu_Anghelescu added a lot of NoConcatenate into your script, but I would recommend you to adjust the syntax to this:

TAB1_TEMP:
LOAD
AutoNumber(ID & NAME & DATE) AS %_KEY_XA,
*
FROM $(QVDPath)INCR1.qvd (QVD);

TAB2:
NoConcatenate LOAD
AutoNumber(ID & NAME & DATE) AS %_KEY_XA,
*
FROM $(QVDPath)INCR2.qvd (QVD);

OUTER JOIN(TAB1_TEMP)
NoConcatenate LOAD
*
RESIDENT TAB2;

NON_MATCH:
NoConcatenate LOAD
*
RESIDENT TAB1_TEMP
WHERE ISNULL(ID);

TAB1:
NoConcatenate LOAD
*
RESIDENT TAB1_TEMP
WHERE NOT ISNULL(ID);

NoConcatenate disables the feature that Qlik Sense always concatenate existing tables if there exists an table with the exact same collection of field names. Read more on NoConcatenate here: https://help.qlik.com/sv-SE/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/NoConcatenate.htm

Re: Outer with QVD files

Black_Hole — Tue, 18 Jun 2019 15:32:34 GMT

Hello @Claudiu_Anghelescu , @Vegar ,

Thank you both for your help.

My problem had been resolved successfully!