2 Replies Latest reply: Jul 10, 2013 5:55 PM by Stephen Redmond RSS

    Exclude an Invoice

    Jennie Elliott

      Here is my script:

       

      InvoiceHistoryLineItem:
      LOAD TIHLBRAND,
           TIHLBRDCST,
           TIHLCLSPRD,
           TIHLNUMBRD,
           TIHLNUMCST,
           TIHLNUMINV,
           TIHLNUMLIN,
           TIHLOPCST,
           TIHLOPDTE,
           TIHLOPFET,
           TIHLOPINNB,
           TIHLOPQTY,
           TIHLOPVNNB,
           TIHLYRPRIN
      FROM
      [C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\VH\InvoiceHistoryLineItem.csv]
      (txt, utf8, embedded labels, delimiter is ',', msq);

      Store * from InvoiceHistoryLineItem into [QVD\VH\InvoiceHistoryLineItem_2.qvd] (qvd);
      Drop Table InvoiceHistoryLineItem;

       

      I need to exclude one invoice number (4175709) with bad numbers using the TIHLNUMINV field. There are over 3M records so manually doing this is not an option.

       

      How would I complete this?

       

      Thanks in Advance!

        • Re: Exclude an Invoice
          Stefan Wühl

          Maybe like

           

          InvoiceHistoryLineItem:
          LOAD TIHLBRAND,
               TIHLBRDCST,
               TIHLCLSPRD,
               TIHLNUMBRD,
               TIHLNUMCST,
               TIHLNUMINV,
               TIHLNUMLIN,
               TIHLOPCST,
               TIHLOPDTE,
               TIHLOPFET,
               TIHLOPINNB,
               TIHLOPQTY,
               TIHLOPVNNB,
               TIHLYRPRIN
          FROM
          [C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\VH\InvoiceHistoryLineItem.csv]
          (txt, utf8, embedded labels, delimiter is ',', msq)

          WHERE TIHLNUMINV <> 4175709;

           

          Store * from InvoiceHistoryLineItem into [QVD\VH\InvoiceHistoryLineItem_2.qvd] (qvd);

           

          Drop Table InvoiceHistoryLineItem;

          • Re: Exclude an Invoice
            Stephen Redmond

            Hi,

             

            Is this not just as easy as putting a where clause into the load from the CSV:

             

            ...

            FROM

            [C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\VH\InvoiceHistoryLineItem.csv]

            (txt, utf8, embedded labels, delimiter is ',', msq)

            Where TIHLNUMINV <> 4175709;

             

             

            Regards,

             

             

            Stephen

             

            Stephen Redmond is author of QlikView for Developer's Cookbook

            He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.

            Follow me on Twitter: @stephencredmond