Replicate a SQL logic in Qlik

pranubitla — Sat, 16 Nov 2024 16:52:28 GMT

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

Re: Replicate a SQL logic in Qlik

mikaelsc — Wed, 29 Dec 2021 20:53:35 GMT

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...

Re: Replicate a SQL logic in Qlik

marcus_sommer — Mon, 03 Jan 2022 18:09:34 GMT

I think this will be helpful:

Renaming fields in QlikView using Mapping Load | | Learn QlikView (learnallbi.com)

- Marcus

topic Re: Replicate a SQL logic in Qlik in App Development

Replicate a SQL logic in Qlik

Re: Replicate a SQL logic in Qlik

Re: Replicate a SQL logic in Qlik