Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Hi!!
My intention is do this in the LOAD using qlikview joins, not with a sql query.
but thank you for your attention
can u provide the data in excel file as i can tell you how to perform through qlikview script
Hi Muhammad!
Here is the file.
The tables are in the sheets.
Thxs!
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.
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!
Hi Fernando
Please find the attached file. If this is not your desired result then let me know
Hi Fernando
Please find the attached file. If this is not your desired result then let me know
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;