converting sql logic into qlikview

[pawwy1415]

Hi Experts,

We've requirement to convert attach SQL script into qlikview..  I have attached SQL script.. Most of the logic change into qlikview but can someone help to convert part 1 and 2 below in attach logic..

file attach - sql text

and qlikview which already written

1. {fn ltrim( {fn concat(CAST(PerformanceRecordDate.Fiscal_Year AS INTEGER) , {fn concat('/', {fn ltrim(CAST(PerformanceRecordDate.Fiscal_Year AS INTEGER)+1)})} )})}

2. ( ( (PerformanceRecordDate.Fiscal_Year * 100 ) + PerformanceRecordDate.Fiscal_Week_No ) in (SELECT yrwk (INTEGER) FROM

(SELECT ((Fiscal_Year * 100) + Fiscal_Week_No ) AS yrwk, ROW_NUMBER() OVER (ORDER BY yrwk DESC) AS rn

FROM v_calendar

QUALIFY rn BETWEEN 1 AND 13

WHERE calendar_dt BETWEEN

(SELECT min(Calendar_Dt) FROM v_Calendar WHERE fiscal_year ='       2015'

AND fiscal_week_no = 11) - ((6)-1)*7

AND

(SELECT min(Calendar_Dt) FROM v_Calendar WHERE fiscal_year = '       2015'

AND fiscal_week_no = 11)

GROUP BY Fiscal_Year, Fiscal_Week_No)  as yrwktbl)  )

  )

[petter · Accepted solution]

1. 1. Is simply a concatenation of this year with last year using / in between - like this:
            2015/2014      
   
   This would be a good guess on how to write it in QlikView:
   
        Trim( PerformanceRecordDate.Fiscal_Year ) & '/' & (Num( PerformanceRecordDate.Fiscal_Year ) - 1). 
    
2. The IN clause will give a list of 13 numbers which are the last (DESC is effecting that) thirteen weeks of         the fiscal year. The fiscal year and weeks are determined by the WHERE clause which seems to have probably two constants that might  be dynamic. This is hard to see in the SQL because the tool that originally created the SQL dynamically converts a couple of things into constants. The two probable constant/dynamic numbers are 11 and (6) in the WHERE clause.
   
   If a human wrote this SQL it wouldn't write ((6)-1)*7  but rather write 35. So obviously the (6) must be a dynamic value...
   
   Anyhow the IN clause considers a window of 36 weeks where only the 13 last ones are candidates for the 13 numbers in the IN clause's list.
   
   This could be written in several ways in a QlikView Load Script - and probably much less convoluted than this SQL - which very well might be tool-generated and not generated directly by a coder.
   
   My choice in QlikView would be to make a "helper-table" first that will contain the 13 weeks and use the Exists()-function in a subsequent LOAD to filter the weeks to be included.