Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pawwy1415
Contributor II

converting sql logic into qlikview

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

  )

1 Solution

Accepted Solutions
MVP
MVP

Re: converting sql logic into qlikview

  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.
1 Reply
MVP
MVP

Re: converting sql logic into qlikview

  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.
Community Browser