Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HELP PLEASE
try this:
sum(if(Rcount != 0 ,0,if(isnull(H.documentno),C.gross-C.amont,0)))
will you plese tell me about this one also
SUM(DECODE(Rcount,0,DECODE(documentno,NULL,0,(Gross- Amount)),(Gross- Amount)))
@sunny_talwar - Hi Sunny - is this something you can look at?
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
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
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:
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))
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
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