Need help on the chnaging sql query to qlikview

[chinnu123]

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

3)

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.

[Anil_Babu_Samineni]

Perhaps this?

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

OR

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;

[chinnu123]

Hi Anil,

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

Thanks,

Chinnu.

[vishsaggi]

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

1.

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

2.

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;

[jaimeaguilar]

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

[dinuwanbr]

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