decode in qliksense

[capriconuser]

hi 

how i use decode in qliksense .. this is the expression 

SUM(DECODE(Rcount,0,DECODE(H.documentno,NULL,(C.gross- C.amont),0),0))

how to use decode in qliksense ?

any help 

[capriconuser]

HELP PLEASE

[andykrst]

try this:

sum(if(Rcount != 0 ,0,if(isnull(H.documentno),C.gross-C.amont,0)))

[capriconuser]

@andykrst 

will you plese tell me about this one also 

SUM(DECODE(Rcount,0,DECODE(documentno,NULL,0,(Gross- Amount)),(Gross- Amount))) 

[Michael_Tarallo]

@sunny_talwar - Hi Sunny - is this something you can look at?

[JuanGerardo]

Hi @andykrst , you can use Pick() and Match() functions to have a similar result in Qlik Sense, for example:

SUM(Pick(Match(Rcount, 0), Pick(Match(IsNull(documentno), True(), False()), 0, Gross - Amount)))

JG

[capriconuser]

i am trying to change this 

SUM(DECODE(Rcount,0,DECODE(documentno,NULL,0,(Gross- Amount)),(Gross- Amount)))

to this

sum({<[Year] = {"$(=vYear)"},[Rcount ] = {">0"},[Table1.Flag] = {'USA'},[Table2.Flag] = {'USA'} >} if(Rcount = 0, if(isNull([documentno]),0,
(gross- amount)),(gross- amount)),0)

but this shows an error that

Sum takes 1 parameter

@JuanGerardo @Michael_Tarallo @andykrst 

[JuanGerardo]

Your expression is not syntactically correct, what is your logic? this is the main point here as you have to implement it with Set Analysis instead of SQL. I guess you need the sum of Gross-Amount when any of these conditions:

• Rcount = 0 (Rcount = {0})
• AND documentno is not null (documentno = {"*"})
• Rcount <> 0 (Rcount -= {0})

So you condition for Rcount>0 is not compatible with this logic. It seems you are looking for something similar to this:

sum({<[Year] = {"$(=vYear)"},[Rcount ] = {0},[Table1.Flag] = {'USA'},[Table2.Flag] = {'USA'}, documentno={"*"} >} (gross - amount))
+
sum({<[Year] = {"$(=vYear)"},[Rcount ] -= {0},[Table1.Flag] = {'USA'},[Table2.Flag] = {'USA'}>} (gross - amount))

[capriconuser]

i need to convert this sql into qliksense expression 

SUM(DECODE(Rcount,0,DECODE(documentno,NULL,0,(Gross- Amount)),(Gross- Amount)))

and in ur answer why you write expression two times .. is this possible to combined both into 1 statement @JuanGerardo 

[JuanGerardo]

When you use set analysis, this is for chart expressions not for script. If you need the same logic in your script, you can use in the SQL sentence so you tell the database to run the decode:

//Connect to MyDB;

Load Data;

SQL SELECT SUM(DECODE(Rcount,0,DECODE(documentno,NULL,0,(Gross- Amount)),(Gross- Amount))) AS Data

FROM ...;

Or you can implement the logic in the Qlik Sense Load statement:

Load Sum(Gross) - Sum(Amount)

....

Where (Rcount = 0 And not Isnull(documentno)) Or Rcount <> 0;

JG