8 Replies Latest reply: Feb 22, 2011 6:14 PM by John Witherspoon RSS

    Why does this IF (Exists not work?

    Joe Kirwan

      Here is my script:

      ___________________________________

      SORDTEMP:

      LOAD REF as SORDREF

      FROM

      [C:\intlocal\QVW Target Files\SORDERS1.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',')

      WHERE DATE > '31/12/2010';

       

      SORDITEM:

      LOAD IF (Exists (SORDREF,REF), REF) as SORDREF,

      ITEMREF,

      ACCCODE,

      NOMCODE

      FROM

      [C:\INTLOCAL\QVW Target Files\SORDITEM1.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',');

       

      ________________

      Note - SORDTEMP - only loads orders that have been created in 2011 from the Order Header fle SORDERS1.csv.

      SORDITEM - should load details fron file SORDITEM1.csv, but ONLY for those orders for which an order REF has been loaded into SORDTEMP

      Any suggestions?



        • Why does this IF (Exists not work?
          John Witherspoon

          The second load will load all records from the CSV. All you're doing is nulling out the SORDREF field when it wasn't in the other file, but the other fields will all load in. To avoid loading the rows at all, use where(exits(...)) like this:

          SORDITEM:
          LOAD REF as SORDREF,
          ITEMREF,
          ACCCODE,
          NOMCODE
          FROM
          [C:\INTLOCAL\QVW Target Files\SORDITEM1.csv]
          (txt, codepage is 1252, embedded labels, delimiter is ',')
          WHERE exists(SORDREF,REF)
          ;

          • Why does this IF (Exists not work?
            Vlad Gutkovsky

            Move the exists() condition to a WHERE statement after the second table load, instead of an IF statement within the load itself.

            Regards,

            • Why does this IF (Exists not work?
              Karl Pover

              Try doing one following:

              SORDITEM:

              LOAD REF as SORDREF,

              ITEMREF,

              ACCCODE,

              NOMCODE

              FROM

              [C:\INTLOCAL\QVW Target Files\SORDITEM1.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',')

              where Exists (SORDREF,REF);

               

              or

               

              SORDITEM:

              RIGHT KEEP (SORDTEMP)

              LOAD REF as SORDREF,

              ITEMREF,

              ACCCODE,

              NOMCODE

              FROM

              [C:\INTLOCAL\QVW Target Files\SORDITEM1.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',');

               

              Each way should only load the REF's in SORDITEM that are also in SORDTEMP.

               

              Regards.



                • Why does this IF (Exists not work?
                  Joe Kirwan

                  Hi Karl

                  The first script worked for me. The second didn't.

                  The second loads all records from file SORDITEM1.csv

                  Joe

                    • Why does this IF (Exists not work?
                      Karl Pover

                      Yeap, you're right. I can't seem to get my left and right straight. You should use a Left Keep instead of a Right Keep.

                      I mentioned this solution because it is sometimes faster when loading large QVD's because the where statement will slow the QVD load while the Left Keep will let the QVD load fast and then filter out the rows you don't need.

                      However, a quick test reveals that a "where exists" allows the the QVD to loaded as QVD Optimized (Fast), but a "where field=value" slows done the QVD load.

                      Regards.

                        • Why does this IF (Exists not work?
                          John Witherspoon

                           


                          Karl Pover wrote:...a quick test reveals that a "where exists" allows the the QVD to loaded as QVD Optimized (Fast), but a "where field=value" slows done the QVD load.


                          Yep, you're allowed a single "where exists" in an optimized load. So far as I know, that's the ONLY thing you can put in the "where" without slowing it down. Even a second "where exists" will slow it down.

                    • Why does this IF (Exists not work?
                      Karl Pover

                      Edit: Where did the option to delete a post go???

                       

                      Try doing one following:

                      SORDITEM:

                      LOAD REF as SORDREF,

                      ITEMREF,

                      ACCCODE,

                      NOMCODE

                      FROM

                      [C:\INTLOCAL\QVW Target Files\SORDITEM1.csv]

                      (txt, codepage is 1252, embedded labels, delimiter is ',')

                      where Exists (SORDREF,REF);

                       

                      or

                       

                      SORDITEM:

                      RIGHT KEEP (SORDTEMP)

                      LOAD REF as SORDREF,

                      ITEMREF,

                      ACCCODE,

                      NOMCODE

                      FROM

                      [C:\INTLOCAL\QVW Target Files\SORDITEM1.csv]

                      (txt, codepage is 1252, embedded labels, delimiter is ',');

                       

                      Each way should only load the REF's in SORDITEM that are also in SORDTEMP.

                       

                      Regards.