QlikSense Data Load Migration - SQLServer to HANA connection

Wed, 02 Mar 2016 21:59:22 GMT

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

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.

topic QlikSense Data Load Migration - SQLServer to HANA connection in Connectivity & Data Prep

QlikSense Data Load Migration - SQLServer to HANA connection