topic Re: Concatenate Where not Exists() in QlikView

Concatenate Where not Exists()

Mon, 27 Jan 2014 13:07:29 GMT

Hi all

I have two tables that I would like to concatenate. Table names and columns are as follows

tblTechValMeasure:

SYS_ID

,MEASURE_INDEX

,TechValDate

tblTechValStatus:

SYS_ID

,TechValDate

If a SYS_ID exists in tblTechValMeasure then I do not want to concatenate a row from tblTechValStatus with a corresponding SYS_ID, so I would like a final table of unique SYS_IDs

Here is the code from my script.

Concatenate (tblTechValMeasure)

LOAD

SYS_ID

,TechValDate

Resident

tblTechValStatus

Where

not Exists ('SYS_ID')

I am getting duplicate SYS_IDs in the resulting table. Can anyone help me as to why this is happening?

Note that the code snippets above are in the actual order that they are in my script, but they do not follow each other directly.

Re: Concatenate Where not Exists()

alexandros17 — Mon, 27 Jan 2014 13:10:39 GMT

Add an instruction noConcatenate after loading:

tblTechValMeasure:

noconcatenate

SYS_ID

,MEASURE_INDEX

,TechValDate

tblTechValStatus:

noconcatenate

SYS_ID

,TechValDate

and at the end write

Drop table tblTechValStatus;

Re: Concatenate Where not Exists()

Peter_Cammaert — Mon, 27 Jan 2014 13:11:34 GMT

Remove the quotes around SYS_ID as exists() parameter.

Peter

Re: Concatenate Where not Exists()

Mon, 27 Jan 2014 13:45:36 GMT

Hi James , I am suspecting the table tblTechValStatus already concatenated to tblTechValMeasure in the initial load may be they both have same structure.

If you are loading the data directly from DB. Please try like below:

############# From DB ##############

tblTechValMeasure:

SELECT SYS_ID

,MEASURE_INDEX

,TechValDate

From tblTechValMeasure ;

CONCATENATE (tblTechValMeasure)

LOAD SYS_ID

,TechValDate

Where not Exists (SYS_ID);

SELECT SYS_ID ,

TechValDate

FROM tblTechValStatus;

############# From QVD ###########

tblTechValMeasure:

LOAD SYS_ID

,MEASURE_INDEX

,TechValDate

From tblTechValMeasure.qvd (qvd) ;

CONCATENATE (tblTechValMeasure)

LOAD SYS_ID

,TechValDate

FROM tblTechValStatus.qvd (qvd)

Where not Exists (SYS_ID);

Re: Concatenate Where not Exists()

Mon, 27 Jan 2014 14:31:55 GMT

Thanks guys.

It was as simple as Peter suggested... Removing the single quotes worked!

An embarassing error but I am new to QlikView!