Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
pranubitla
Contributor II
Contributor II

Replicate a SQL logic in Qlik

I'm trying to replicate below SQL code in Qlik qvd layer. please can anyone help

code is [

case when Fieldname like 'det.MOT-F%'   then 'Mortgage'||replace(Fieldname ,'det.MOT-F-',' - ') 
when Fieldname like 'det.MOT-V%'    then 'Variable Deposit'||replace(Fieldname 'det.MOT-V-',' - ')
when Fieldname like 'det.TGO-%'    then 'Deposit'   

 else 'Undefined ' Fieldname    end    as   NEW_Fieldname       ]

 

Basically need to replace first few characters in the Fieldname column 

if(Replace(Fieldname, ' det.MOT-F- ' ' - ' , 'Mortgage  - '), IF(Replace(Fieldname, ' det.MOT-V- ' ' - ' , 'Variable Deposit  - '), if (Wildmatch(Fieldname, '*det.TGO-F*'),   'Deposit' )   as   NEW_Fieldname

 

Fieldname Expecting New_Field
det.MOT-F-SUBSCRIPTION Mortgage  - SUBSCRIPTION
det.MOT-F-SUBSCRIPTION2 Mortgage  - SUBSCRIPTION2
det.MOT-V-ENERGY (HI) Variable Deposit - ENERGY (HI)
det.MOT-V-COMM (LOW) Variable Deposit  - COMM (LOW)
det.MOT-V-ENERGY-COST (HI) Variable Deposit - ENERGY-COST (HI)
det.TGO-F-LOAN Deposit  
det.TGO-F-LOAN-DEPOSIT Deposit  
   
Labels (1)
2 Replies
mikaelsc
Specialist
Specialist

some idea's: 

pick(wildmatch(fieldname,'det.MOT-F*','det.MOT-V*','det.TGO-F*','Mortgage','Variable','Deposit')

&

subfield(fieldname,'-',-1) 

 

use and combine them with some extra if's... but most of it is there...

marcus_sommer