topic Re: if logic help in QlikView

if logic help

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me to convert below SQL logic into Qlik.

(Case

WHEN [STRINGVALUE] IS NOT NULL THEN CAST([STRINGVALUE] as NVARCHAR(1024))

WHEN [DATEVALUE] IS NOT NULL THEN CAST([DATEVALUE] as NVARCHAR(1024))

WHEN [NUMBERVALUE] IS NOT NULL THEN CAST([NUMBERVALUE] as NVARCHAR(1024))

WHEN [TEXTVALUE] IS NOT NULL THEN CAST([TEXTVALUE] as NVARCHAR(4000))

ELSE " "

END) as MAINVALUE

Thanks in advance.

Re: if logic help

MK_QSL — Fri, 09 Mar 2018 10:59:37 GMT

You can use the same Logic while using SQL code inside Qlik Script.

and store it in QVD.

Re: if logic help

jonathandienst — Fri, 09 Mar 2018 11:41:44 GMT

You can do this in QV, but you will be loading much more data (4 fields instead of 1):

If(IsNull(STRINGVALUE) = 0, STRINGVALUE,

if(IsNull(DATEVALUE) = 0, DATEVALUE,

If(IsNull(NUMBERVALUE) = 0, NUMBERVALUE,

If(IsNull(TEXTVALUE) = 0, TEXTVALUE, ' ')))) as MainValue

Re: if logic help

prma7799 — Fri, 09 Mar 2018 11:57:08 GMT

have you try this

try this

if (len([STRINGVALUE]) > 0 , Num([STRINGVALUE] ) ,

if(len([DATEVALUE])>0 , Num([DATEVALUE]) ,

if(len( [NUMBERVALUE]) >0 , Num([NUMBERVALUE] ) ,

if(len( [TEXTVALUE] ) > 0 , Num([TEXTVALUE])

, '' ))))

Re: if logic help

jonathandienst — Fri, 09 Mar 2018 11:58:13 GMT

QV is not strongly typed like SQL, so there is no need to cast the type. The default type is text (char) and there is no real limit on the length. IsNull()=0 is the QV equivalent of IS NOT NULL.

If you prefer the SQL approach, leave the SQL query in place and call that from QV.

Re: if logic help

jonathandienst — Fri, 09 Mar 2018 11:59:42 GMT

The OP's question is about casting to NVARCHAR (character), so Num() is not the correct function.

Re: if logic help

prma7799 — Fri, 09 Mar 2018 12:02:17 GMT

then this will work ??

if (len([STRINGVALUE]) > 0 , ([STRINGVALUE] ) ,

if(len([DATEVALUE])>0 , ([DATEVALUE]) ,

if(len( [NUMBERVALUE]) >0 , ([NUMBERVALUE] ) ,

if(len( [TEXTVALUE] ) > 0 , ([TEXTVALUE])

, '' ))))

Re: if logic help

prma7799 — Fri, 09 Mar 2018 12:03:05 GMT

try with this

if (len([STRINGVALUE]) > 0 , ([STRINGVALUE] ) ,

if(len([DATEVALUE])>0 , ([DATEVALUE]) ,

if(len( [NUMBERVALUE]) >0 , ([NUMBERVALUE] ) ,

if(len( [TEXTVALUE] ) > 0 , ([TEXTVALUE])

, '' ))))