17 Replies Latest reply: May 30, 2018 1:55 AM by Christoph Albrecht RSS

    Where not Exists() clause with resident load

    Christoph Albrecht

      I have for example two tables:

       

      test1:

      NoConcatenate

      LOAD * INLINE [

      F1, F2

      a, 210

      ];


      test2:

      NoConcatenate

      LOAD * INLINE [

      F1, F2

      a, 212

      b, 422

      f, 424

      h, 24

      h, 242

      g, 561

      g, 562

      g, 563

      ];


      Now I would like to concatenate the two tables, but with the "Where not Exists" clause.


      Concatenate(test1)

      Load *

      Resident test2

      Where not Exists(F1);


      Drop Table test2;


      The expected result should be:


      test1:

      F1, F2

      a, 210

      b, 422

      f, 424

      h, 24

      g, 561

       

      But the the result now is:

       

      test1:

      F1, F2

      a, 210

       

      When I am doing it like this, it is working as expected:

       

      test1:

      LOAD * INLINE [

      F1, F2

      a, 210

      ];


      LOAD * INLINE [

      F1, F2

      a, 212

      b, 422

      f, 424

      h, 24

      h, 242

      g, 561

      g, 562

      g, 563

      ]

      Where not Exists(F1);


      What is wrong with my first version? What is the difference? And how can I use two resident tables to get the expected result?

        • Re: Where not Exists() clause with resident load
          Bram Knuever

          Because you have already loaded the data. it actually exists already. Try using a dummy field in the first table and concat the second table with where not exists on that.

          • Re: Where not Exists() clause with resident load
            Sunny Talwar

            The problem is that by the time you do the load with concatenate, the values for F1 already include all the data from table F1. But in the case it is working, F1 only includes a from test1. Does that make sense? To use resident... you can try this

             

            test1:

            NoConcatenate

            LOAD * INLINE [

            F1, F2

            a, 210

            ];


            test2:

            NoConcatenate

            LOAD F1 as F1_Temp,

                 F2;

            LOAD * INLINE [

            F1, F2

            a, 212

            b, 422

            f, 424

            h, 24

            h, 242

            g, 561

            g, 562

            g, 563

            ];


            Concatenate(test1)

            Load F1_Temp as F1,

                 F2

            Resident test2

            Where not Exists(F1, F1_Temp);


            Drop Table test2;

             

            First you rename F1 to call it F1_Temp and this was F1 will only still have one value (a) and then check F1_Temp against F1 in your where exist statement

              • Re: Where not Exists() clause with resident load
                Christoph Albrecht

                Your example is working great! Thanks.

                 

                But it is not working, if test2 table is not an inline load but a data load from qvd file. :/

                  • Re: Where not Exists() clause with resident load
                    Sunny Talwar

                    You mean something like this doesn't work?

                     

                    test1:

                    NoConcatenate

                    LOAD * INLINE [

                    F1, F2

                    a, 210

                    ];

                     

                    test2:

                    NoConcatenate

                    LOAD F1 as F1_Temp,

                        F2

                    FROM XYZ.qvd (qvd);

                     

                    Concatenate(test1)

                    Load F1_Temp as F1,

                        F2

                    Resident test2

                    Where not Exists(F1, F1_Temp);

                     

                    Drop Table test2;

                      • Re: Where not Exists() clause with resident load
                        Christoph Albrecht

                        I have a simulation for incremental load. For the first run, I load the data into a table and after that I staore the table in a QVD file. For the next run, I load another data and concatenate the loaded data with the qvd data.

                         

                        One difference is, that the data of the testtable2 is not an inline load. It is from another resident table (testtable1).

                         

                        if (IsNull(QvdCreateTime('lib://Data/testtable'))) then


                        testtable1:

                        NoConcatenate

                        LOAD * INLINE [

                            F1, F2

                            a, 212

                            b, 422

                            f, 424

                            h, 25

                            g, 561

                        ];


                        testtable2:

                        NoConcatenate

                        Load *

                        Resident testtable1;


                        else


                        testtable1:

                        NoConcatenate

                        LOAD * INLINE [

                            F1, F2

                            a, 210

                            z, 600

                        ];


                        testtable2:

                        NoConcatenate

                        Load *

                        Resident testtable1;


                        end if


                        if not(IsNull(QvdCreateTime('lib://Data/testtable2'))) then

                         

                          testtable2_Tmp:

                          NoConcatenate

                          Load

                          F1 as F1_Tmp,

                            F2

                          FROM 'lib://Data/testtable2' (qvd);

                         

                          Concatenate(testtable2)   

                          Load

                          F1_Tmp as F1,

                            F2

                          Resident testtable2_Tmp

                          Where not Exists(F1, F1_Tmp);

                         

                          Drop Table testtable2_Tmp;

                        end if


                        Store testtable2 into 'lib://Data/testtable2' (qvd);


                        Drop Table testtable1;


                        You have to run the example two times. After the second run the result table should be:


                        testtable2:

                        F1, F2

                        a, 210

                        z, 600

                        b, 422

                        f, 424

                        h, 25

                        g, 561

                          • Re: Where not Exists() clause with resident load
                            Sunny Talwar

                            I ran the same exact thing in QlikView...and it seemed to have worked for me (with just one change)

                             

                            IF (IsNull(QvdCreateTime('testtable2.qvd'))) then


                            testtable1:

                            NoConcatenate

                            LOAD * INLINE [

                                 F1, F2

                                 a, 212

                                 b, 422

                                 f, 424

                                 h, 25

                                 g, 561

                            ];

                             

                            testtable2:

                            NoConcatenate

                            Load *

                            Resident testtable1;


                            ELSE


                            testtable1:

                            NoConcatenate

                            LOAD * INLINE [

                                 F1, F2

                                 a, 210

                                 z, 600

                            ];


                            testtable2:

                            NoConcatenate

                            Load *

                            Resident testtable1;


                            ENDIF


                            IF not(IsNull(QvdCreateTime('testtable2.qvd'))) then


                            testtable2_Tmp:

                            NoConcatenate

                            LOAD F1 as F1_Tmp,

                            F2

                            FROM 'testtable2.qvd' (qvd);

                             

                            Concatenate(testtable2)  

                            LOAD F1_Tmp as F1,

                            F2

                            Resident testtable2_Tmp

                            Where not Exists(F1, F1_Tmp);


                            Drop Table testtable2_Tmp;


                            ENDIF


                            STORE testtable2 into 'testtable2.qvd' (qvd);

                            DROP Table testtable1;

                             

                            Capture.PNG

                             

                            In your code, is this testtable or testtable2?

                             

                            Capture.PNG

                              • Re: Where not Exists() clause with resident load
                                Christoph Albrecht

                                Oh, that was the mistake in my example.

                                 

                                But my real world program (similar script with other data) is still not running. I have to check the code again (after checking it already 1000 times). But now I know it should work.

                                • Re: Where not Exists() clause with resident load
                                  Christoph Albrecht

                                  But this is also working:

                                   

                                  if (IsNull(QvdCreateTime('lib://Data/testtable2'))) then


                                  testtable1:

                                  NoConcatenate

                                  LOAD * INLINE [

                                      F1, F2

                                      a, 212

                                      b, 422

                                      f, 424

                                      h, 25

                                      g, 561

                                  ];


                                  testtable2:

                                  NoConcatenate

                                  Load

                                  F1,

                                  F2

                                  Resident testtable1;


                                  else


                                  testtable1:

                                  NoConcatenate

                                  LOAD * INLINE [

                                      F1, F2

                                  ];


                                  testtable2:

                                  NoConcatenate

                                  Load

                                  F1,

                                  F2

                                  Resident testtable1;


                                  end if


                                  if not(IsNull(QvdCreateTime('lib://Data/testtable2'))) then

                                   

                                    Concatenate(testtable2)

                                    Load

                                    F1,

                                      F2

                                    FROM 'lib://Data/testtable2' (qvd)

                                    Where not Exists(F1);

                                   

                                  end if


                                  Store testtable2 into 'lib://Data/testtable2' (qvd);


                                  Drop Table testtable1;

                        • Re: Where not Exists() clause with resident load
                          Christoph Albrecht

                          So I cannot find any difference to my previous posted examples. This is my original code:

                           

                          $(vTableName):

                          NoConcatenate

                          Load Distinct

                              X1 & '|' & X2 & '|' & X3 as [%SessionIdDimKey],

                              X1 & '|' & X2 & '|' & X3 as [Unique Session Id],

                              X4 as [Session Number]           

                          Resident [TmpDataJoinTable]

                          Where not IsNull(ocpp_session_id);


                          // store and join data in/from qvd file

                          let vQvdPath = vDataPathPrefix & '$(vTableName).qvd';


                          if not(IsNull(QvdCreateTime(vQvdPath))) then

                           

                            $(vTableName)_Tmp:

                            NoConcatenate

                            Load

                            [%SessionIdDimKey] as [%SessionIdDimKey_Tmp],

                              [Unique Session Id],

                              [Session Number] 

                            FROM [$(vQvdPath)] (qvd);

                           

                            Concatenate($(vTableName))   

                            Load

                            [%SessionIdDimKey_Tmp] as [%SessionIdDimKey],

                              [Unique Session Id],

                              [Session Number] 

                            Resident $(vTableName)_Tmp

                            Where not Exists([%SessionIdDimKey], [%SessionIdDimKey_Tmp]);

                           

                            Drop Table $(vTableName)_Tmp;

                          end if


                          Store [$(vTableName)] into [$(vQvdPath)] (qvd);


                          When I remove the where clause, it is working. With where clause not.