if logic help

[mahitham]

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.

[MK_QSL]

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

and store it in QVD.

[jonathandienst]

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

[prma7799]

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])

, '' ))))

[jonathandienst]

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.

[jonathandienst]

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

[prma7799]

then this will work ??

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

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

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

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

, '' ))))

[prma7799 · Accepted solution]

try with this

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

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

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

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

, '' ))))