topic Need help on the chnaging sql query to qlikview in QlikView

Need help on the chnaging sql query to qlikview

chinnu123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

As I am not good in sql I am facing issues in changing sql conditions to qlik.

Can anyone spend some time on the below three conditions.

1)case when i.INCIDENT_ID is null or i.INCIDENT_ID like '0000%00' then NULL else substr( i.INCIDENT_ID,LENGTH( i.INCIDENT_ID)-8,9)

2) case when abc10 ='RTN' then 'RTN - ' || Open else close

KPI_SERVICE as

(

select KEY key, STATUS, RISK_STATUS, row_number() over (partition by key order by TARGET_DATE desc) record_num from

Tableabc

)

Thanks in advance,

Thanks,

Chinnu.

Re: Need help on the chnaging sql query to qlikview

Anil_Babu_Samineni — Mon, 14 Aug 2017 13:01:02 GMT

Perhaps this?

1) If(IsNull(i.INCIDENT) or Match(i.INCIDENT, '0000%00'), Null(), Mid(i.INCIDENT_ID, Len(i.INCIDENT)-8, 9))

If(IsNull(i.INCIDENT) or (i.INCIDENT LIKE '0000%00'), Null(), Mid(i.INCIDENT_ID, Len(i.INCIDENT)-8, 9))

2) If(Match(abc10, 'RTN', 'RTN' & '||' & Open, Close)

3) Not sure, I understand this? Try something like below

Sample:

SQL select KEY, STATUS, RISK_STATUS, TARGET_DATE, row_number() over (partition by key order by TARGET_DATE desc) record_num from Tableabc;

Left Join (Sample)

Load KEY as key, STATUS, RISK_STATUS, Count(KEY) as record_num order by TARGET_DATE DESC;

Re: Need help on the chnaging sql query to qlikview

chinnu123 — Thu, 24 Aug 2017 20:02:30 GMT

Hi Anil,

Thanks for your reply..but unfortunately the above expressions are not working..

Thanks,

Chinnu.

Re: Need help on the chnaging sql query to qlikview

vishsaggi — Thu, 24 Aug 2017 20:39:01 GMT

May try these? Change them accordingly if i missed any.

IF(Isnull(INCIDENT_ID) = -1 OR Match(INCIDENT_ID, '000%00'), Null(), Mid(INCIDENT_ID, Len(INCIDENT_ID)-8, 9)) AS Condition1,

IF(abc10 = 'RTN', 'RTN - '& Open, Close) AS Condition2 INLINE [

3. Reference taken from here: Thanks to johnw for his expertise. -> ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'

LOAD

Key, STATUS, RISK_STATUS,

if(Key = previous(Key), peek("Row Number")+1,1) as "Row Number"

RESIDENT [Data]

ORDER BY Key, TARGET_DATE DESC;

SQL SELECT KEY AS key,

STATUS,

RISK_STATUS,

TARGET_DATE

FROM yourdb.dbo.tablename;

Re: Need help on the chnaging sql query to qlikview

jaimeaguilar — Thu, 24 Aug 2017 21:12:35 GMT

Hi,

have you tried running your SQL query directly in qlikview?

you can do something like this in QlikView:

QV_Table:

Load Field1, Field2;

SQL Query... //You can use the SQL statement and after that you can execute almost any SQL Query/procedure

Hope this helps

best regards

Re: Need help on the chnaging sql query to qlikview

dinuwanbr — Fri, 25 Aug 2017 03:11:23 GMT

Hi,

Instead of a part of the query can you upload the whole query?

Otherwise try to execute the same sql query from qlik side using the keyword "Sql"

Ex. Sql Select * from .....

As per my knowledge qlik doesn't support some of sql keywords like "Union", "Union all", etc.

Rgds,

Dinu1