11 Replies Latest reply: Sep 12, 2013 1:59 AM by John Kusters RSS

    Left Join load with where clause

    John Kusters

      Hi,

       

      I am working on a problem for some days now, but can’t find the solution.

       

      The problem is as follows: ( I also attach the word doc with same description, but maybe more readable)

       

      I have 2 tables A and B which I want to use in my QV script to make a new result
      table C.

       

      Tabel A: Table with saleslines per Dessin, having a Salesdate per line.

       

       


       

      Dessin


       

       

      Salesno


       

       

      Salesdate


       

       

      1020


       

       

      1


       

       

      01-06-2003


       

       

      1021


       

       

      2


       

       

      01-06-2006


       

       

      1022


       

       

      3


       

       

      26-06-2013


       

       

      1021


       

       

      4


       

       

      01-07-2010


       

       

      1023


       

       

      5


       

       

      01-06-2011


       

       

       

      Tabel B:
      Table with exclusivity periods per dessin. As you can see 1 dessin (1021) can
      be exclusive in more then 1 period, but there is no overlap in different
      periods in table B for the same Dessin.

       

       


       

      Dessin


       

       

      desStartdate


       

       

      desEnddate


       

       

      1020


       

       

      01-03-2003


       

       

      01-03-2004


       

       

      1021


       

       

      01-03-2006


       

       

      01-03-2007


       

       

      1021


       

       

      01-03-2010


       

       

      01-03-2011


       

       

      1023


       

       

      01-03-2004


       

       

      01-03-2005


       

       

      1023


       

       

      01-03-2007


       

       

      01-03-2008


       

       

       

      Problem:
      For each line from table A I want to aff a column ‘Exclusive’ with value ‘Y’ or
      ‘N’

       

      If A.Salesdate >= B.desStartdate and A.salesdate <= B.desEnddate THEN ‘Y’
      ELSE ‘N’

       

      If I do a left join the result table C will look like This:

       

       


       

      Dessin


       

       

      Salesno


       

       

      Salesdate


       

       

      Exclusive


       

       

      1020


       

       

      1


       

       

      01-06-2003


       

       

      Y


       

       

      1021


       

       

      2


       

       

      01-06-2006


       

       

      Y


       

       

      1021


       

       

      2


       

       

      01-06-2006


       

       

      N


       

       

      1022


       

       

      3


       

       

      26-06-2013


       

       

      -


       

       

      1021


       

       

      4


       

       

      01-07-2010


       

       

      N


       

       

      1021


       

       

      4


       

       

      01-07-2010


       

       

      Y


       

       

      1023


       

       

      5


       

       

      01-06-2011


       

       

      N


       

       

      1023


       

       

      5


       

       

      01-06-2011


       

       

      N


       

       

       

      But what I
      want is:

       

       


       

      Dessin


       

       

      Salesno


       

       

      Salesdate


       

       

      Exclusive


       

       

      1020


       

       

      1


       

       

      01-06-2003


       

       

      Y


       

       

      1021


       

       

      2


       

       

      01-06-2010


       

       

      Y


       

       

      1022


       

       

      3


       

       

      26-06-2013


       

       

      N


       

       

      1021


       

       

      4


       

       

      01-07-2010


       

       

      Y


       

       

      1023


       

       

      5


       

       

      01-06-2011


       

       

      N


       

       

       

      I tried a lot of things in the QV-script:

      One of the things I tried was a left join with a where clause saying:
      where Salesdate >= desStartdate And Salesdate <= ;

      But when I reload the script gives an error that Salesdate field is not found.

      Please can anyone tell me how to do this in my QV script.