Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

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

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);



1 Solution

Accepted Solutions
Not applicable

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

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;


View solution in original post

2 Replies
Not applicable

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

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;


View solution in original post

Not applicable

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

Sure helps!

Thanks,

Aldo.