7 Replies Latest reply: Nov 28, 2016 3:30 PM by John Witherspoon RSS

    How to exclude double data from different QVD?

    Stefan Spiker

      Hi,

       

      I need to exclude the orderid where the plan_date and OrderDate matches. The only problem is that the plan_date and the OrderDate data are in different qvd. Now I joined these two together like this:

       

      Datums:

      LOAD orderid,

                plan_date

                From [DatabaseA.qvd]

      (qvd);

      Inner Join (Datums)

               LOAD orderid,

                         OrderDate

                         FROM [DatabaseB.qvd]

      (qvd);

       

      orderidplan_dateOrderDate
      131-1-20154-1-2016
      228-11-20154-1-2016
      316-12-20154-1-2016
      418-12-20154-1-2016
      521-12-20154-1-2016
      621-12-20154-1-2016
      721-12-20154-1-2016
      82-1-20164-1-2016
      94-1-20164-1-2016
      104-1-20164-1-2016

                                                                

      131-1-20154-1-2016
      228-11-20154-1-2016
      316-12-20154-1-2016
      418-12-20154-1-2016
      521-12-20154-1-2016
      621-12-20154-1-2016
      721-12-20154-1-2016
      82-1-20164-1-2016
      94-1-20164-1-2016
      104-1-20164-1-2016

       

      The last two orderid's have the same plan_date and OrderDate. I want to exclude these two orderid's, but I have no idea how I have to do that.

       

      Could anyone help me?

       

      Thanks!

        • Re: How to exclude double data from different QVD?
          John Witherspoon

          If orderid is a unique key (I'm assuming you accidentally duplicated all the rows in the post):

           

          INNER JOIN (Datums)
          LOAD orderid
          RESIDENT Datums
          WHERE plan_date <> OrderDate
          ;

          • Re: How to exclude double data from different QVD?
            Massimo Grossi

            after the join you ca n do a resident load of your table excluding the rows with the same date

             

            noconcatenate

            load

                 *

            resident

                 Datums

            where

                 not (plan_date = OrderDate)

                 ;

            • Re: How to exclude double data from different QVD?
              Anand Chouhan

              This way also you can try

               

              LOAD * Inline

              [

              orderid, plan_date, OrderDate

              1, 31-1-2015, 4-1-2016

              2, 28-11-2015, 4-1-2016

              3, 16-12-2015, 4-1-2016

              4, 18-12-2015, 4-1-2016

              5, 21-12-2015, 4-1-2016

              6, 21-12-2015, 4-1-2016

              7, 21-12-2015, 4-1-2016

              8, 2-1-2016, 4-1-2016

              9, 4-1-2016, 4-1-2016

              10, 4-1-2016, 4-1-2016

              ];

               

              NoConcatenate

              LOAD

              *

              Resident Data

              Where (plan_date <> OrderDate);

              DROP Table Data;

               

               

              Regards,

              Anand

              • Re: How to exclude double data from different QVD?
                Ruben Marin

                Hi Stefan, another option:

                Datums:

                LOAD orderid,

                          plan_date,

                          orderid & '#' & plan_date as check

                          From [DatabaseA.qvd]

                (qvd);

                Inner Join (Datums)

                        LOAD orderid,

                                  OrderDate

                                  FROM [DatabaseB.qvd] Where not exists('check', orderid & '#' & OrderDate)

                (qvd);

                 

                DROP Field check;

                 

                I didn't tested but the inner join should also remove the rows from the first table.

                • Re: How to exclude double data from different QVD?
                  Stefan Spiker

                  I am sorry guys, I think i should have explained it better, so here is attempt number two.

                   

                  Here is my original qvw with 4 different QVD's:

                  table A.png

                  What I want is to remove the orderid's out of my qvw where the plan_date and the OrderDate have the same date. You can see these two things are in two different QVD's: OrderDate is in 'Planning' and plan_date is in 'Afhaalwagen'.

                   

                  I have made a new table where I put the orderid, plan_date and OrderDate in one table, but now I get synthetic keys.

                   

                  Datums:

                  LOAD orderid,

                            plan_date

                  FROM $(vPath)afhaalwagennummer.qvd (qvd);

                   

                  inner join

                   

                  Load orderid,

                          OrderDate

                  FROM $(vPath)planning.qvd (qvd);

                   

                  NewTable:

                  NoConcatenate

                  LOAD orderid,

                       plan_date,

                       OrderDate

                  RESIDENT Datums

                  WHERE not (plan_date = OrderDate);

                  Drop Table Datums;

                   

                  Table B.png

                   

                  The problem is, in the new table the orderid with the same plan_date and OrderDate is hidden, but these orderid's are still in the whole qvw.

                   

                  What I actually want is the following:

                   

                  Table C.png

                   

                  Am I thinking it totally wrong? Because I have the feeling I am close, but I can't get it clear.

                   

                  Thanks.

                    • Re: How to exclude double data from different QVD?
                      John Witherspoon

                      Is orderid a unique key to all three tables? If so, join Afhaalwagen and Indelingen onto the Planning table, then use my previous inner join suggestion. But I suspect that each has a different unique key, even if they all share orderid, and so you'd want to keep them separate. If so, perhaps something like this:

                       

                      [Delete Orders]:
                      LOAD
                      orderid as [Delete Order]
                      ,plan_date
                      FROM $(vPath)afhaalwagennummer.qvd (qvd);

                      INNER JOIN ([Delete Orders])
                      LOAD
                      orderid as [Delete Order]
                      ,OrderDate
                      FROM $(vPath)planning.qvd (qvd);

                      INNER JOIN ([Delete Order])
                      LOAD *
                      WHERE plan_date = OrderDate;

                      INNER JOIN (Planning)
                      LOAD your key fields
                      RESIDENT Planning
                      WHERE not exists([Delete Order],orderid);

                      INNER JOIN (Afhaalwagen)
                      LOAD your key fields
                      RESIDENT Afhaalwagen
                      WHERE not exists([Delete Order],orderid);

                      INNER JOIN (Indelingen)
                      LOAD your key fields
                      RESIDENT Indelingen
                      WHERE not exists([Delete Order],orderid);

                      DROP TABLE [Delete Orders];