Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
tetew89
Contributor II
Contributor II

replicate SQL query into Qlik sense

jh

Labels (1)
1 Reply
theoat
Partner - Creator III
Partner - Creator III

Try something like this :

tmp:
LOAD 
    [Month],
    [code],
    [brand],
    SUM([SUnits]) AS [SUnits],
    SUM([Values]) AS [Values],
    SUM([Units])  AS [Units]
FROM dbo.Table
GROUP BY [Month], [code], [brand];

tmp2:
LOAD *,
    Num(Left([Month], 4)) AS [YEAR],
    If(Right([Month], 3) = 'Jan', 1,
    If(Right([Month], 3) = 'Feb', 2,
    If(Right([Month], 3) = 'Mar', 3,
    If(Right([Month], 3) = 'Apr', 4,
    If(Right([Month], 3) = 'May', 5,
    If(Right([Month], 3) = 'Jun', 6,
    If(Right([Month], 3) = 'Jul', 7,
    If(Right([Month], 3) = 'Aug', 8,
    If(Right([Month], 3) = 'Sep', 9,
    If(Right([Month], 3) = 'Oct', 10,
    If(Right([Month], 3) = 'Nov', 11, 12))))))))))) AS [MONTH]
RESIDENT tmp;

tmp3:
LOAD t.*,
    (SELECT SUM([SUnits]) 
    FROM tmp2 t2 
    WHERE t.code = t2.code AND t.brand = t2.brand
    AND t.YEAR = t2.YEAR AND t.MONTH >= t2.MONTH) as SUnits_YTD
RESIDENT tmp2;

tmp4:
LOAD t.*,
    (SELECT TOP 1 SUnits_YTD 
    FROM tmp3 t2 
    WHERE t.code = t2.code AND t.brand = t2.brand AND t.MONTH = t2.MONTH AND t.YEAR = (t2.YEAR+1)) AS SUnits_LYTD
RESIDENT tmp3;

tmp5:
LOAD t.*,
    RANK() OVER (PARTITION BY code, Month ORDER BY (SUnits_YTD-SUnits_LYTD)/SUnits_LYTD DESC) AS Rank_growthSUnits
RESIDENT tmp4
WHERE NOT ISNULL(SUnits_LYTD) OR SUnits_LYTD <> 0;

FINAL_RESULT:
LOAD 
    Month, 
    code, 
    brand, 
    SUnits, 
    Values, 
    Units, 
    SUnits_YTD, 
    SUnits_LYTD, 
    Rank_growthSUnits
RESIDENT tmp5;

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.