11 Replies Latest reply: Mar 27, 2014 2:44 PM by Fernando Tonial RSS

    Join Max Date

    Fernando Martini

      Hi!!

       

      I've to join two table by the itemID and Sale/Purchase Date.

      In this join I have to connect the ItemID from table Sale with the corresponding ItemID in the table Stock where the last PurshaseDate is <= SaleDate.

      can someone help me?

      sale_stock.jpg

        • Re: Join Max Date

          Hi Fernando,

           

          You would need a query as below:

           

          SELECT sa.ItemID, sa.SaleDate, st2.PurchaseDate, sa.SalePrice, st2.ItemCost

          FROM Sale sa

          INNER JOIN (SELECT MAX(PurchaseDate) AS "MaxPurchaseDate", ItemID FROM Stock GROUP BY ItemID) st

          ON sa.ItemID = st.ItemID

            AND st.MaxPurchaseDate <= sa.SaleDate

          INNER JOIN Stock st2

          ON st2.ItemID = st.ItemID

            AND st2.PurchaseDate = st.MaxPurchaseDate

           

          JoinMaxDateAnswer.jpg

          • Re: Join Max Date
            Fernando Tonial

            You need join tables and after make a load resident with your where.

            like this:

             

            StockTable_TMP:
            LOAD
                ItemID,
                PurchaseDate,
                ItemCost
            From StockTable.QVD (qvd);
            
            Join (StockTable_TMP)
            LOAD
                ItemID,
                SaleDate,
                SalePrice
            From Sale_Table.qvd (qvd);
            
            Sale_Cost_Table:
            NoConcatenate
            LOAD
                ItemID,
                PurchaseDate,
                ItemCost,
                SaleDate,
                SalePrice
            Resident StockTable_TMP
            Where PurchaseDate <= SaleDate;
            
            DROP Table StockTable_TMP;
            
            
            

             

            Best Regards.

            Tonial.

              • Re: Join Max Date
                Fernando Martini

                Opa!! Dae Fernando!

                 

                Se eu fizer dessa maneira, todos os registros menores ou iguais a SaleDate entrarão no join.

                Minha intenção é fazer um Max no PurchaseDate, trazendo apenas o registro imediatamente menos ou igual a SaleDate.

                 

                Abraço!

                  • Re: Re: Re: Join Max Date
                    Ashraf Muhammad Arsalan Ashraf

                    Stock:

                    LOAD ItemID,

                        PurchaseDate,

                         ItemCost,

                         'Purchase' as Flag

                    FROM

                    [Sales Data.xlsx]

                    (ooxml, embedded labels, table is Stock)

                     

                     

                    ;

                     

                     

                    join

                     

                     

                     

                     

                    LOAD ItemID,

                         SalesDate,

                         SalesPrice,

                         'Sales' as Flag

                    FROM

                    [Sales Data.xlsx]

                    (ooxml, embedded labels, table is Sales);

                     

                     

                    Store Stock into SalesCostTable.qvd(qvd);

                     

                     

                     

                     

                    SalesCostTable:

                    LOAD ItemID,

                         PurchaseDate,

                         ItemCost,

                         SalesDate,

                         SalesPrice,

                         Flag

                    FROM

                    SalesCostTable.qvd

                    (qvd) where PurchaseDate <= SalesDate;

                    • Re: Re: Join Max Date
                      Fernando Tonial

                      Olá Fernando!

                       

                      Consegui utilizando o IntervalMatch.

                      Segue Script e anexo.

                       

                      Purchase_TMP:
                      LOAD 
                          ItemID,  
                          ItemCost,
                          PurchaseDate
                      FROM
                      tables.xlsx
                      (ooxml, embedded labels, table is Stock_Table)
                      Where not IsNull (ItemID);
                      
                      Purchase:
                      LOAD 
                          ItemID,  
                          ItemCost,
                          PurchaseDate    As PurchaseDateStart,
                          If(ItemID<>Peek(ItemID),Date(Today()),Date(Peek(PurchaseDateStart)-1)) as PurchaseDateEnd
                      Resident Purchase_TMP 
                      Order by ItemID asc, PurchaseDate desc;
                      
                      DROP Table Purchase_TMP;
                      
                      
                      MinMaxDate:
                      Load Min(PurchaseDateStart) as MinDate, Max(PurchaseDateEnd) as MaxDate resident Purchase;
                      
                      Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
                      Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;
                      
                      
                      Date:
                      Load Date(recno()+$(vMinDate)) as SaleDate Autogenerate vMaxDate - vMinDate;
                      
                      DROP Table MinMaxDate;
                      Join (Purchase)
                      IntervalMatch (SaleDate) LOAD PurchaseDateStart, PurchaseDateEnd  Resident Purchase;
                      
                      
                      Inner Join (Purchase)
                      LOAD ItemID, 
                           SaleDate, 
                           SalePrice
                      FROM
                      tables.xlsx
                      (ooxml, embedded labels, table is Sale_Table);
                      
                      DROP Table Date;
                      
                      // Only for check values
                      QUALIFY *;
                      Stock:
                      LOAD 
                          ItemID,  
                          ItemCost,
                          PurchaseDate
                      FROM
                      tables.xlsx
                      (ooxml, embedded labels, table is Stock_Table);
                      
                      Sale:
                      LOAD ItemID, 
                           SaleDate, 
                           SalePrice
                      FROM
                      tables.xlsx
                      (ooxml, embedded labels, table is Sale_Table);
                      
                  • Re: Join Max Date
                    Ashraf Muhammad Arsalan Ashraf

                    Hi Fernando

                    Please find the attached file. If this is not your desired result then let me know