Join Max Date

[#1]

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?

[#2]

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

[#3]

Hi!!

My intention is do this in the LOAD using qlikview joins, not with a sql query.

but thank you for your attention

[arsal_90]

can u provide the data in excel file as i can tell you how to perform through qlikview script

[#5]

Hi Muhammad!

Here is the file.

The tables are in the sheets.

Thxs!

[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.

[#7]

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!

[arsal_90]

Hi Fernando

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

[arsal_90]

Hi Fernando

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

[arsal_90]

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;