Need help to translate T-SQL Code to QLIKVIEW Code - LEFT OUTER JOIN - Subqueries

[#1]

Hi guys,

I am new in the world of QlikView.

I need to "translate" some code from T-SQL (MS SQL Server 2005) to QlikView code.

Any help will be appreciated.

Thanks in advance,

Aldo.

Below my T-SQL code

SELECT * FROM ITEMS
LEFT OUTER JOIN PRICELISTS ON (ITEMS.ITEMKEY = PRICELISTS.ITEMKEY) AND PRICELISTS.PRICELISTNUMBER IN (3)
 AND PRICELISTS.DATF =
 (SELECT Max(DATF) AS MDate FROM PRICELISTS
 WHERE 1 = 1 AND (PRICELISTS.ITEMKEY = '314001524010') AND (PRICELISTS.PRICELISTNUMBER = 3)
 GROUP BY PRICELISTS.PRICELISTNUMBER, PRICELISTS.ITEMKEY)
WHERE ITEMS.ITEMKEY = '314001524010'

And Below the Code I am trying to create in QLIKVIEW.. I need to add the block that retrieves only the pricelist with the latest date.

Below my QLIKVIEW Code (partial)

SET HashPriceListCost = 3;
SET SalePrice = 1;
CONNECT TO [Provider=...);
ITEMS:
SQL SELECT
 ITEMKEY, ITEMNAME
FROM MDT2006.dbo.ITEMS
WHERE 1 = 1 And ITEMKEY In ('314001524010');
PRL:
SQL SELECT
 DATF,
 ITEMKEY, PRICE, PRICELISTNUMBER
FROM MDT2006.dbo.PRICELISTS
WHERE 1 = 1 And ITEMKEY In ('314001524010') And PRICELISTNUMBER = $(HashPriceListCost);

[#2 · Accepted solution]

Hi Aldo,

Here you are a possible solution, the join will be done through the DatF fields.

I hope this helps you.

Best regards.

SET HashPriceListCost = 3;
SET SalePrice = 1;
CONNECT TO [Provider=...);
Items:
SELECT *
FROM ITEMS
WHERE ITEMS.ITEMKEY = '314001524010'
Left Join
//MaxDate:
 SELECT Max(DATF) AS DatF
 FROM PRICELISTS
 WHERE 1 = 1
 AND (ITEMKEY = '314001524010')
 AND (PRICELISTNUMBER = 3)
 GROUP BY PRICELISTS.PRICELISTNUMBER, PRICELISTS.ITEMKEY;

[#3]

Sure helps!

Thanks,

Aldo.