Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Concatenate Where not Exists()

Remove the quotes around SYS_ID as exists() parameter.

Peter

4 Replies

Re: Concatenate Where not Exists()

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

Remove the quotes around SYS_ID as exists() parameter.

Peter

Not applicable

Re: Concatenate Where not Exists()

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

Re: Concatenate Where not Exists()

Thanks guys.

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

An embarassing error but I am new to QlikView!

Community Browser