Discussion board where members can get started with QlikView.
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
select KEY key, STATUS, RISK_STATUS, row_number() over (partition by key order by TARGET_DATE desc) record_num from
Thanks in advance,
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
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;
Thanks for your reply..but unfortunately the above expressions are not working..
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'
Key, STATUS, RISK_STATUS,
if(Key = previous(Key), peek("Row Number")+1,1) as "Row Number"
ORDER BY Key, TARGET_DATE DESC;
SQL SELECT KEY AS key,
have you tried running your SQL query directly in qlikview?
you can do something like this in QlikView:
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
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.