1 Reply Latest reply: Sep 18, 2015 12:32 AM by Petter Skjolden RSS

    converting sql logic into qlikview

    P Kumar

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

        )

        • Re: converting sql logic into qlikview
          Petter Skjolden
          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.