Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenate Where not Exists()

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.

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Remove the quotes around SYS_ID as exists() parameter.

Peter

View solution in original post

4 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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;

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Remove the quotes around SYS_ID as exists() parameter.

Peter

Not applicable
Author

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);

Not applicable
Author

Thanks guys.

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

An embarassing error but I am new to QlikView!