4 Replies Latest reply: Jan 27, 2014 9:31 AM by James Green RSS

    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.

        • Re: Concatenate Where not Exists()
          Alessandro Saccone

          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

            Remove the quotes around SYS_ID as exists() parameter.

             

            Peter

            • Re: Concatenate Where not Exists()
              Srikanth P

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

                Thanks guys.

                 

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

                 

                An embarassing error but I am new to QlikView!