3 Replies Latest reply: Oct 1, 2012 4:36 AM by Juan Pedro Hidalgo RSS

    Excluding Specific Data

    Juan Pedro Hidalgo

      Hey everyone,

       

      my first week with QV was great and i found a lot of help here in the community so thank everyone for that

       

      I fixed all the problems my report had except one that is not even a real problem but just an annoying thing ^^.

       

      Description: The report is about Requisition's - Purchuase Orders - Invoices - Payments.

      Requisitions Purchuase Orders and Invoices are perfect and i visualize all the needed data perfectly.

      Instead in the payments part i have all the payments, even the one's not linked to any requisition or purchuase order (random example electricity invoice).

      This falses my result when there is no selection made.

      The aggregation system of QV at this point does something wird. Group all this invoices without project in a project " - " and shows the amount of the invoice, the invoice_id but not the invoice_num (even if is present in oracle) and marks it again with a " - ".

      I cant make a SQL condition on the script like where project_id = NULL because in the table i use is not valorized and all the project_id are null so the query wont return anything.

       

      Does anyone have an idea on how i can exclude this data from my report. A script condition will be even better so the data is not loaded at all.

       

      Thanks everyone.

       

      Regards,

      Juan Pedro

        • Re: Excluding Specific Data
          Stefan Wühl

          If payments is linked to Invoices, and you want to load only payments records with a valid link you can try something like

           

           

          INVOICES:

          LOAD * FROM INVOICES;

           

          PAYMENTS:

          LEFT KEEP (INVOICES) LOAD * FROM PAYMENTS;

           

          Or use exists() function in a where clause to load only records with an existing value in Table Invoices from Table Payments.

           

          Please also check out Henric's blog post:

           

          http://community.qlik.com/docs/DOC-3412

           

          Hope this helps,

          Stefan

          • Re: Excluding Specific Data
            Jonathan Brough

            Hi JP,

            "I have all the payments, even the one's not linked to any requisition".

            Add a WHERE EXISTS clause to the end of your LOAD statement. Something like this:

             

            RequisitionTable:

            LOAD

               ReqNo,

               ReqNo AS TestReqNo,

               ReqDate,

               CustCode,

                ....

            FROM .....etc;

             

            PaymentsTable:

            LOAD

               PaymentReqNo AS ReqNo,

               PayDate,

               ....

            FROM .....

            WHERE EXISTS (TestReqNo,PaymentReqNo);

            DROP FIELD TestReqNo;

             

            Note the use of a Test field as the first parameter, which is later dropped. You need to use the unchanged PaymentReqNo field name as the second parameter as that is what is available during the load.

            Also note that with both tables having multiple common fields (date and key) you may want to CONCATENATE them into a TransactionTable.

            Regards,

            Jonathan

            • Re: Excluding Specific Data
              Juan Pedro Hidalgo

              Hey thnks everyone for the tips .

               

              I used the exists clause but i had to add a table to my selection to make it work. I didnt used the the extra field you suggested but i guess my english isnt that good to explain the problem.

               

              cheers

              Juan Pedro