4 Replies Latest reply: Apr 8, 2010 1:27 PM by John Witherspoon RSS

    Remove Row from Table

      Dear All,

      During script running I've to remove some rows from a table.

      Instead to put the "where" with all the AND, I need to use a different way, 'cause the items I've to remove are a lot.

      I found these way looking to some other forum example but I don't know if it's the fastest and the best one.

       

      Just an example how I've tried to do (I've to exclude value u find in INLINE table for field PSTYV)

      I've used this

       

      ExcludeIndia:

      LOAD * INLINE [

      PSTYV

      ZFKP

      ZHA2

      ZJNT

      ZLWM

      ZMTO

      ];

       

      MapTable:

      MAPPING LOAD

      PSTYV,

      1 AS DONT_LOAD

      RESIDENT ExcludeIndia;

       

       

       

      InvoicePosition:

      LOAD CHARG,

      FKIMG,

      KZWI1,

      KZWI2,

      MATNR,

      POSNR,

      PSTYV,

      FROM

      C:\D\PersonalTraining\Qlikview\Test\EsempiDiTest\FATT_POSIZIONI.xlsx

      (ooxml, embedded labels, table is Sheet1)

      WHERE ApplyMap('MapTable',PSTYV,0) = 0;



       

      It works but I don't know if there are other better solution to achieve the same result.

       

      Thanks

      Sergio

        • Remove Row from Table
          Miguel Angel Baeyens de Arce

          Hello Sergio,

          I'm actually using expressions like yours. Working with maps and applymap is fine for me and very elegant and easy to trace should you need to change anything in your code.

          Regards.

            • Remove Row from Table
              John Witherspoon

              You shouldn't need the map in this case, which may speed it up slightly:

              ExcludeIndia:
              LOAD * INLINE [
              ExcludedPSTYV
              ZFKP
              ZHA2
              ZJNT
              ZLWM
              ZMTO
              ];
              InvoicePosition:
              LOAD CHARG,
              FKIMG,
              KZWI1,
              KZWI2,
              MATNR,
              POSNR,
              PSTYV,
              FROM
              C:\D\PersonalTraining\Qlikview\Test\EsempiDiTest\FATT_POSIZIONI.xlsx
              (ooxml, embedded labels, table is Sheet1)
              WHERE NOT EXISTS(ExcludedPSTYV,PSTYV);

              DROP TABLE ExcludeIndia;

                • Remove Row from Table
                  Miguel Angel Baeyens de Arce

                  Good one John. I'm using that too. Thanks!

                    • Remove Row from Table
                      John Witherspoon

                      I doubt this would be any better in this particular case, but just in case, let's say you have a list of all PSTYV codes, whatever they are. It might be faster to do this:

                      ExcludeIndia:
                      LOAD * INLINE [
                      ExcludedPSTYV
                      ZFKP
                      ZHA2
                      ZJNT
                      ZLWM
                      ZMTO
                      ];
                      InvoicePosition:
                      LOAD PSTYV
                      FROM your source for all PSTYV codes
                      WHERE NOT EXISTS(ExcludedPSTYV,PSTYV);

                      DROP TABLE ExcludeIndia;

                      INNER JOIN (InvoicePosition)
                      LOAD CHARG,
                      FKIMG,
                      KZWI1,
                      KZWI2,
                      MATNR,
                      POSNR,
                      PSTYV,
                      FROM
                      C:\D\PersonalTraining\Qlikview\Test\EsempiDiTest\FATT_POSIZIONI.xlsx
                      (ooxml, embedded labels, table is Sheet1);

                      There's no reason for me to think it would be faster. In fact, it looks like it would be slightly slower. But you never know. Maybe an inner join is more efficient than a "not exists()", and would make up for the wasted time doing an extra load. I doubt it, but having alternatives is a good thing.