5 Replies Latest reply: Nov 30, 2012 6:38 AM by Nayan Lalla RSS

    How to eliminate duplicate lines

    Nayan Lalla

      Hi

       

      I am working on a QV Model to determine orders that are invoiced , partially invoiced and outstanding. 

       

      I have 4  tables namely:

      • Order Header
      • Order Detail
      • Invoice Header
      • Invoice Detail

       

      When an order is invoiced , it generates an invoice number, otherwise invoice number  its shown as "****NEW***. 

       

      However i have the following issue where the result I am achieving there are duplicates per line for orders that are invoiced.  See attached excel file called "Outcome Results"

       

      • The duplicate lines are highlighted in yellow in Table called "Current Result"
      • The ideal result i want to achieve is in Table called "Ideal Result"

       

      How do I apply the scripting so that I dont have duplicates.

       

      I have attached the excel file where the table sits as well as the QV Model

       

      Please can you help

       

      Thank you

       

      kind regards

      Nayan

       

       

       

       

       

        • Re: How to eliminate duplicate lines
          Peter Cammaert

          Your model has everything thrown together in a single table. That means that all lines with an OrderID occur twice:

          • once from the invoices data
          • once from the orders data

          That is, they occur twice if there are corresponding invoices. For example ORD002 has no invoices, so no duplicates. Use Table Viewer->Preview to check your data.

           

          Your solution is to use QlikView as ... QlikView: construct an associative model with a few tables and appropriate keys.

           

          Best,

           

          Peter

            • Re: How to eliminate duplicate lines
              Nayan Lalla

              Hi Peter

               

               

              Thank you for your rersponse.

               

              I am new at Qlikview and at scripting, hence I do not how to create an associative model. I just know basics.  In the model that i have attached previously, can you assist with the scripting.

               

              Thank you

               

              kind regards

              Nayan

                • Re: How to eliminate duplicate lines
                  Peter Cammaert

                  Hi Nayan,

                   

                  try this:

                   

                  • Load and join orders and orderlines in a table and call this one Orders
                  • Load and join invoices and invoicelines in a second table and call this one Invoices.
                  • While loading this last table, give all fields a prefix to avoid linking them to the corresponding fields in Orders.
                  • Except for OrderID. That field should be used as a key tying the two together.

                   

                  Teaching you how to write Load scripts would take me too far. But hey, QlikView has excellent Designer/Developer trainings that teach you how to master QlikView in a few days. Make sure to look into them if you want to do some serious development in QlikView.

                   

                  Peter