I want to convert the below SQL query in Qlik Sense Load Script editor. Can some one please guide me the best approach.
SELECT A.*,B.PLN_YR,B.CAT_TYP,B.SALES FROM (
SELECT PLN_YR,PLN_NM,CAT_TYP,MANAGER_NAME,SUM(SALES_AMOUNT) AS SALES FROM FRUITS_TABLE
WHERE CAT_TYP ='APPLES' AND CTG_NM LIKE '%Grapes%' AND MANAGER_NAME IN('David','Sam') AND PLN_YR IN('2010')
GROUP BY PLN_YR,PLN_NM,CAT_TYP,MANAGER_NAME) A
JOIN
(SELECT PLN_YR,PLN_NM,CAT_TYP,SUM(SALES_AMOUNT) AS SALES FROM FRUITS_TABLE
WHERE CAT_TYP ='ORANGES' AND CTG_NM_1 LIKE '%MANGOS%' AND PLN_YR IN('2009') AND PLN_NM IN(SELECT PLN_NM FROM FRUITS_TABLE
WHERE CAT_TYP ='APPLES' AND CTG_NM LIKE '%Grapes%' AND MANAGER_NAME IN('David','sam') AND PLN_YR IN('2010') )
GROUP BY PLN_YR,PLN_NM,CAT_TYP) B
ON A. PLN_NM =B.PLN_NM
More Details below:
Thanks,
-Vidya
Qlik is not so good at aggregating, so it's better to do im on SQL side.
Tomasz