0 Replies Latest reply: Mar 2, 2016 4:59 PM by Peter Casanova RSS

    QlikSense Data Load Migration - SQLServer to HANA connection

    Peter Casanova

      Hey all,

       

      I'm a new QlikSense developer and I have an issue I've been trying to tackle. 

       

      I have a fully deployed application connected to SQLServer (I don't know what version, and it isn't confirmed... this is a legacy server that the project team hasn't worked with - mostly positive it is coming from SQL Server connection).

       

      My issue is on a join in the data load editor between 3 different tables with 3 different alias indicators. All field names have changed from lower case in SQLServer to UpperCase in the HANA connection, so all have been changed (besides those fields already being given new alias for business logic).

       

      Here is the code:

       

       

      LIB CONNECT TO 'DRIA';

       

       

      SQL SELECT distinct kg.ID as 'keyID', 'X' as 'mostRecent'

      INTO #T1

      FROM "_SYS_BIC"."VW_KEYS_GENERATED" pmA OUTER APPLY

      (select top 1 kg.ID from VW_KEYS_GENERATED kg

        left outer join VW_OPTIONS_USED ou

        on kg.ID = ou.KEYID

        where pmA.SERIALNO = kg.SERIALNO

        and ou.CHARACTERISTIC = 'PROTECTION'

        and ou.VALUE = 'TRUE'

        order by kg.DATETIME desc) kg ;

       

       

      SELECT ou.[ID]

            ,ou.[KEYID]

            ,[TYPE]

            ,[CATEGORY]

            ,[CHARACTERISTIC]

            ,kg.SERIALNO

            ,ouY.VALUE as 'PMTypeActual',

                  case CHARACTERISTIC when '' then CATEGORY + ' - ' +

                  case ou.VALUE when '' then 'Not Installed'

                  else ou.[VALUE] end

                  else CATEGORY + '-' + CHARACTERISTIC + ' - ' +

                  case ou.VALUE when '' then 'Not Installed'

                  else ou.[VALUE] end

                  end as 'indCategory'

            ,case ou.VALUE when '' then 'Not Installed' else ou.[VALUE] end as 'value'

            ,ISNULL(ouY.VALUE, 0) as 'PMTypeValue2'

            ,ISNULL(ouZ.VALUE, 0) as 'PMTypeSpec'

            ,ISNULL(ouX.SERIALNO2, 0) as 'PMSerialNo'

        into #t2

       

        FROM "_SYS_BIC"."VW_OPTIONS_USED" ou 

        left outer join #t1 as t1 on ou.KEYID = t1.keyID

          outer apply (select top 1 ouX.VALUE from VW_OPTIONS_USED ouX

            where ouX.KEYID = ou.KEYID

            and ouX.TYPE = ou.TYPE

            and ouX.CATEGORY like 'PM Type'

            and VALUE is not null) ouY 

          outer apply (select top 1 ouA.VALUE from VW_OPTIONS_USED ouA

            where ouA.KEYID = ou.KEYID

            and ouA.TYPE = ou.TYPE

            and ouA.CATEGORY like 'PM Type Specification%'

            and VALUE is not null) ouZ

        left outer join VW_KEYS_GENERATED kg on ou.KEYID = kg.ID

          outer apply (select top 1 pma.SERIALNO2 from "_SYS_BIC"."VW_PM_ASSIGN" pma

            where ou.TYPE = pma.LOGICALLOC

            and kg.SERIALNO = pma.SERIALNO) ouX

       

      where category is not null and mostRecent = 'X' ;

       

      select ID,

        KEYID,

              TYPE,

              CATEGORY,

              CHARACTERISTIC,

              indCategory,

              value,

              case PMTypeValue2 when '' then 0

              when null then 0

                  when 'None' then 0

                  when 'Not Installed' then 0

                  when 'Not Available' then 0

                  when 'NULL' then 0

                  when null then 0

                  when 'null' then 0

                  when '0' then 0

                  else 1 end as 'pmTypeValue',

               PMTypeActual,

               PMTypeSpec,

               PMSerialNo,

               ib.MYSYSTEM, 

               REPLACE(LTRIM(REPLACE(ib.SUPERIOR_SERIAL_NO,'0',' ')),' ','0') AS superiorSerialNo,

               ib.WARRANTY_STATUS as 'PMWarrantyStatus',

               ib.MYCOMPANY as 'PMCompany',

               ib.FUNCT_NAME as 'PMFab',

               ib.MGMT_REGION as 'PMRegion',

               ib.PRODUCT as 'PMProduct'

      from #t2 left outer join "_SYS_BIC"."VW__INSTALLED_BASE" on PMSerialNo = ib.SERIAL_NO;

       

       

      It is first failing on a syntax error

       

      error message.PNG

       

      I can get past this error by messing around with the syntax of the first load statement.... but it continues to have syntax errors until it errors on missing a FROM: statement.

       

      So a lot of info (and messy code in my opinion, i would rather rewrite) but:

       

           -Do you believe it is failing just because of my syntax, or to your knowledge are any of these statements not supported while loading      from a one-to-one HANA connection?

       

      Any help is very much appreciated.

       

      Thank you.