if logic help

[mahitham]

Hi Experts,

Can any one please help me on replicating below Oracle case logic rankfield and usage field into Qlik.

Note:

I cant use the direct query in the backend. I have to implement in UI only.

Case

when id in (7,8,9) then p.rank

when id != 7 then r.rank

else  null                                     as       rankfield

----------------------------------------------------

Case

   when m.id in (7,8,9,10,11) then

     case

        when r.usd  is null then 1000

         else  r.usd

end

          else null

end                                        as udagefield

--------------------------------------------------------------------------

thanks in advance

[YoussefBelloum]

Hi,

Maybe you can describe the expected output via an image attached here ?

I don't know what is p.rank or r.rank (maybe fields), but In UI I think you can try this:

=If(Match(id,'7','8','9'), p.rank, if(not match(id,'7'), r.rank), 'null')

[mahitham]

Hi Youssef,

Thanks for your reply. this is exactly what I am looking for.

p.rank,r.rank, r.usd ,m.id are fields I have copied the direct query p,r,m are table suffix in oracle.

Could you please help me to convert this as well it contains multiple if's

Case

   when m.id in (7,8,9,10,11) then

     case

        when r.usd  is null then 1000

         else  r.usd

end

          else null

end                                        as udagefield

[YoussefBelloum]

=If(Match(m,'7','8','9','10','11'),

     if(len(trim([r.usd]))=0, '1000', [r.usd]), 'null')

[mahitham]

Hi Youssef,

the below expression is showing  error

=If(Match(id,'7','8','9'), p.rank, if(not match(id,'7'), r.rank), 'null')

[YoussefBelloum]

did you verify that your fields are correctly written ?

Id, p.rank, r.rank...

if yes, try this,

=If(Match(id,'7','8','9'), [p.rank], if(not match(id,'7'), [r.rank]), 'null')

[Chanty4u]

try this way

=If(Match(test1,'7','8','9'), [test1],

if(not match(test1,'7'),[test1],'null'))

[YoussefBelloum · Accepted solution]

sorry I misplaced the else part

=If(Match(id,'7','8','9'), [p.rank], if(not match(id,'7'), [r.rank], 'null'))

Like chanty's expression