Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Need help on the chnaging sql query to qlikview

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.

5 Replies
Highlighted

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Hi Anil,

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

Thanks,

Chinnu.

Highlighted
Champion III
Champion III

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;

Highlighted
Partner
Partner

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

Highlighted
Creator III
Creator III

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