13 Replies Latest reply: Dec 9, 2017 11:39 AM by Bernardo Araujo RSS

    Generating a table from an intervalmatch join

    Bernardo Araujo

      I am trying to create an auxiliary table from two other tables with an "IntervalMatch" serving as connecting rule. But once I join those two tables I cannot dump its contents inside a third table (since I cannot use two "Resident" tables), so I tried to load the other fields from the "Resident" table in the "Inner Join IntervalMatch" but it generates a cartesian product.

      Which would be the best way to generate this third table keeping the interval match logic?

       

       

       

      // Loading columns from "SalesOrderHeader"

      SpecialTemp1:

      Load

      SalesOrderID as Special.SaleID,

          [OrderDate] as Special.OrderDate,

          [TerritoryID] as Special.TerritoryID1,

          [OnlineOrderFlag] as Special.Online

      Resident [SalesOrderHeader];

       

       

      // Loading columns from "SalesTerritoryHistory"

      SpecialTemp2:

      Load

      [TerritoryID] as Special.TerritoryID,

      [BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,

          [SalesTerritoryHistory.StartDate] as Special.StartDate,

          if(

          len(trim(date([SalesTerritoryHistory.EndDate])))=0,

          date('30/06/2009'), // Today()

              date([SalesTerritoryHistory.EndDate])

      ) as Special.EndDate

      Resident [SalesTerritoryHistory];

       

       

      // Joining when "Special.OrderDate" is between "Special.StartDate" and "Special.EndDate"

      Inner Join IntervalMatch ( Special.OrderDate )

      Load

      Special.StartDate,

          Special.EndDate

      Resident SpecialTemp2;

       

       

      // Joining with "Employee"

      Join

      Load

      [BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,

          [Employee.LastName],

          [Employee.FirstName],

          [Employee.MiddleName]

      Resident [Employee];

       

       

      // Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"

      Left Join

      Load

          [Special.Online],

          [Special.OrderDate],

          [Special.SaleID]

      Resident SpecialTemp1;

       

       

      // This attempt requires all the above fields in a single table

      SpecialMetrics:

      Load

      Special.SaleID,

          Special.OrderDate,

          Special.TerritoryID,

          Special.Online,

          if(Special.Online='0', Num(286), Special.SalespersonID) as Employee.Code,

          Special.StartDate,

          Special.EndDate,

          [Employee.LastName],

          [Employee.FirstName],

          [Employee.MiddleName]

      Resident SpecialTemp2;

       

       

      // Dropping temp tables

      Drop Tables SpecialTemp1, SpecialTemp2;

        • Re: Generating a table from an intervalmatch join
          Anil Babu

          May be this?

           

          //Loading columns from "SalesOrderHeader"

          SpecialTemp1:

          Load

          SalesOrderID as Special.SaleID,

              [OrderDate] as Special.OrderDate,

              [TerritoryID] as Special.TerritoryID1,

              [OnlineOrderFlag] as Special.Online

          Resident [SalesOrderHeader];

           

          //Loading columns from "SalesTerritoryHistory"

          SpecialTemp2:

          Load

          [TerritoryID] as Special.TerritoryID,

          [BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,

              [SalesTerritoryHistory.StartDate] as Special.StartDate,

              if(

              len(trim(date([SalesTerritoryHistory.EndDate])))=0,

              date('30/06/2009'), // Today()

                  date([SalesTerritoryHistory.EndDate])

          ) as Special.EndDate

          Resident [SalesTerritoryHistory];


          Inner Join IntervalMatch (Special.OrderDate)

          Load Special.StartDate, Special.EndDate

          Resident SpecialTemp2;


          // Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"

          Left Join (SpecialTemp2)

          Load

              [Special.Online],

              [Special.OrderDate],

              [Special.SaleID]

          Resident SpecialTemp1;


            • Re: Generating a table from an intervalmatch join
              Bernardo Araujo

              Thank you very much for answering!

              Aside from deleting the comment:  "// Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"" I cant find any difference to my code. I need the columns [Special.Online], [Special.OrderDate] and [Special.SaleID] in the table too.

                • Re: Generating a table from an intervalmatch join
                  Anil Babu

                  Diff is here

                   

                  Left Join (SpecialTemp2)

                  Load

                      [Special.Online],

                      [Special.OrderDate],

                      [Special.SaleID]

                  Resident SpecialTemp1;

                    • Re: Generating a table from an intervalmatch join
                      Bernardo Araujo

                      Thanks for clarifying. Unfortunately the problem persists, for each SaleID it has a bunch of TerritoryID. Like if it is not respecting the relation of the "Special.TerritoryID" from [SalesOrderHeader] with the "Special.TerritoryID" from [SalesTerritoryHistory] and it just take all salesperson working in that period regardless of the region.

                       

                      By the way, there is a typo in the firs load:

                      [TerritoryID] as Special.TerritoryID1,

                      is actually:

                      [TerritoryID] as Special.TerritoryID,

                        • Re: Generating a table from an intervalmatch join
                          Anil Babu

                          True, It's because field constraints. And you need to work for that using

                           

                          Left Join (SpecialTemp2)

                          Load

                              [Special.Online],

                              [Special.OrderDate],

                              [Special.SaleID]

                          Resident SpecialTemp1 Where Exists (Special.TerritoryID1, Special.TerritoryID);

                            • Re: Generating a table from an intervalmatch join
                              Bernardo Araujo

                              It won't find the expression from the "exists" function.

                              Field 'Special.TerritoryID' not found

                              I can't figure out why since this field belongs to the SpecialTemp2 table.

                                • Re: Generating a table from an intervalmatch join
                                  Anil Babu

                                  My bad, Can you post whole sctipt in qvs?

                                    • Re: Generating a table from an intervalmatch join
                                      Bernardo Araujo

                                      Sorry, but my last comment is waiting for moderation for the last eight hours due to a link to the code. While the last comment is waiting, here is the script:

                                       

                                      // Loading columns from "SalesOrderHeader"

                                      SpecialTemp1:

                                      Load

                                      SalesOrderID as Special.SaleID,

                                          [OrderDate] as Special.OrderDate,

                                          [TerritoryID] as Special.TerritoryID1,

                                          [OnlineOrderFlag] as Special.Online

                                      Resident [SalesOrderHeader];

                                       

                                       

                                      // Loading columns from "SalesTerritoryHistory"

                                      SpecialTemp2:

                                      Load

                                      [TerritoryID] as Special.TerritoryID,

                                      [BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,

                                          [SalesTerritoryHistory.StartDate] as Special.StartDate,

                                          if(

                                          len(trim(date([SalesTerritoryHistory.EndDate])))=0,

                                          date('30/06/2009'), // Today()

                                              date([SalesTerritoryHistory.EndDate])

                                      ) as Special.EndDate

                                      Resident [SalesTerritoryHistory];

                                       

                                       

                                      // Joining when "Special.OrderDate" is between "Special.StartDate" and "Special.EndDate"

                                      Inner Join IntervalMatch ( Special.OrderDate )

                                      Load

                                      Special.StartDate,

                                          Special.EndDate

                                      Resident SpecialTemp2;

                                       

                                       

                                      // Joining with "Employee"

                                      Join

                                      Load

                                      [BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,

                                          [Employee.LastName],

                                          [Employee.FirstName],

                                          [Employee.MiddleName]

                                      Resident [Employee];

                                       

                                       

                                      // Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"

                                      Left Join (SpecialTemp2)

                                      Load

                                          [Special.Online],

                                          [Special.OrderDate],

                                          [Special.SaleID]

                                      Resident SpecialTemp1

                                      Where Exists (Special.TerritoryID1, "Special.TerritoryID");;

                                       

                                       

                                      // This attempt requires all the above fields in a single table

                                      SpecialMetrics:

                                      Load

                                      Special.SaleID,

                                          Special.OrderDate,

                                          Special.TerritoryID,

                                          Special.Online,

                                          if(Special.Online='0', Num(286), Special.SalespersonID) as Employee.Code,

                                          Special.StartDate,

                                          Special.EndDate,

                                          [Employee.LastName],

                                          [Employee.FirstName],

                                          [Employee.MiddleName]

                                      Resident SpecialTemp2;

                                       

                                       

                                      // Dropping temp tables

                                      Drop Tables SpecialTemp1, SpecialTemp2;

                                          • Re: Generating a table from an intervalmatch join
                                            Rodolfo Souza

                                            Bernardo,

                                            I guess if you just make a simple intervalmatch, it will match histories with no relation. Considering this I think you should use intervalmatch(date ,key)

                                             

                                            your script will be this way:

                                             

                                            SpecialTemp1:

                                            Load

                                            SalesOrderID as Special.SaleID,

                                                [OrderDate] as Special.OrderDate,

                                                [TerritoryID] as Special.TerritoryID1,

                                                [OnlineOrderFlag] as Special.Online

                                            Resident [SalesOrderHeader];

                                             

                                             

                                            // Loading columns from "SalesTerritoryHistory"

                                            SpecialTemp2:

                                            Load

                                            [TerritoryID] as Special.TerritoryID,

                                            [BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,

                                                [SalesTerritoryHistory.StartDate] as Special.StartDate,

                                                if(

                                                len(trim(date([SalesTerritoryHistory.EndDate])))=0,

                                                date('30/06/2009'), // Today()

                                                    date([SalesTerritoryHistory.EndDate])

                                            ) as Special.EndDate

                                            Resident [SalesTerritoryHistory]

                                            Where Exists (Special.TerritoryID1, "TerritoryID");

                                            //this will filter territories that are not in the header

                                             

                                             

                                            // Joining when "Special.OrderDate" is between "Special.StartDate" and "Special.EndDate"

                                            Inner Join IntervalMatch ( Special.OrderDate, Special.TerritoryID1)

                                            Load

                                            Special.TerritoryID as Special.TerritoryID1

                                            Special.StartDate,

                                                Special.EndDate

                                            Resident SpecialTemp2;

                                            //territoryID as Special.TerritoryID1 will be the key of the intervalmatch

                                             

                                            // Joining with "Employee"

                                            Join

                                            Load

                                            [BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,

                                                [Employee.LastName],

                                                [Employee.FirstName],

                                                [Employee.MiddleName]

                                            Resident [Employee];

                                             

                                             

                                            // Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"

                                            Left Join (SpecialTemp2)

                                            Load

                                                [Special.Online],

                                                [Special.OrderDate],

                                                [Special.SaleID]

                                            Resident SpecialTemp1

                                            //Where Exists (Special.TerritoryID1, "Special.TerritoryID") //commented

                                            ;;

                                             

                                             

                                            // This attempt requires all the above fields in a single table

                                            SpecialMetrics:

                                            Load

                                            Special.SaleID,

                                                Special.OrderDate,

                                                Special.TerritoryID,

                                                Special.Online,

                                                if(Special.Online='0', Num(286), Special.SalespersonID) as Employee.Code,

                                                Special.StartDate,

                                                Special.EndDate,

                                                [Employee.LastName],

                                                [Employee.FirstName],

                                                [Employee.MiddleName]

                                            Resident SpecialTemp2;

                                             

                                             

                                            // Dropping temp tables

                                            Drop Tables SpecialTemp1, SpecialTemp2;