Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.