topic Re: Select case in QlikView

Select case

Thu, 03 Sep 2015 13:46:05 GMT

Hi,

I'm trying to convert this case code to Qlikview, could I please get assistance.

CASE

when MAC_CODE = 963 then ''SNL''

Else ''MLOAN''

END as PRODUCT_CODE

CASE when REGISTRATION_MONTH_CODE = ''NOTREG''

then ''N''

else

CASE when ( year(INFORMATION_DATE)*100 + month(INFORMATION_DATE) )

else ''N''

END as REG_LAST_3MONTHS_IND

CASE when FINAL_ML_AMT <= 50000 then ''A''

when FINAL_ML_AMT <= 100000 then ''B''

when FINAL_ML_AMT <= 150000 then ''C''

when FINAL_ML_AMT <= 350000 then ''D''

when FINAL_ML_AMT <= 700000 then ''E''

else ''F''

END as MLOANS_BANDS_CODE

Re: Select case

sinanozdemir — Thu, 03 Sep 2015 14:02:08 GMT

Hi,

Try this:

LOAD

If(MAC_CODE = 963, 'SNL', 'MLOAN') As PRODUCT_CODE,

If(FINAL_ML_AMT <= 50000, 'A',

If(FINAL_ML_AMT <= 100000, 'B',

If(FINAL_ML_AMT <= 150000, 'C',

If(FINAL_ML_AMT <= 350000, 'D',

If(FINAL_ML_AMT <= 700000, 'E', 'F'

))))) As MLOANS_BANDS_CODE

You can put the rest within an IF statement like the above

Re: Select case

Thu, 03 Sep 2015 18:11:48 GMT

Thanks a lot, i will try it out now

Re: Select case

sinanozdemir — Thu, 03 Sep 2015 18:14:34 GMT

Yeah, sure.

I think you are missing a condition in the second CASE scenario:

Re: Select case

Thu, 03 Sep 2015 18:26:08 GMT

I made a mistake, heres the original code:

CASE

when MAC_CODE = 963 then ''SNL''

Else ''MLOAN''

END as PRODUCT_CODE

CASE

when REGISTRATION_MONTH_CODE = ''NOTREG'' then ''N''

else

CASE

when (year(INFORMATION_DATE)*100 + month(INFORMATION_DATE))

else ''N''

end

END as REG_LAST_3MONTHS_IND

CASE

when FINAL_ML_AMT <= 50000 then ''A''

when FINAL_ML_AMT <= 100000 then ''B''

when FINAL_ML_AMT <= 150000 then ''C''

when FINAL_ML_AMT <= 350000 then ''D''

when FINAL_ML_AMT <= 700000 then ''E''

else ''F''

END as MLOANS_BANDS_CODE

Re: Select case

sinanozdemir — Thu, 03 Sep 2015 18:33:09 GMT

You are missing a "then" after the case statement:

Re: Select case

Thu, 03 Sep 2015 19:24:38 GMT

Oh sorry about that, I will resend it again,

Tell me, If want to put a sum on that nested if statement, how do i go about it?

Re: Select case

sinanozdemir — Thu, 03 Sep 2015 19:30:24 GMT

You can sum two columns like the below:

If(Column1 + Column2 <= 10000, 'True', 'False')

When you use the aggregate version of it, Sum():

If(Sum(Column1) <= 1000, 'True', 'False')

Then you need to use Group By in the load script.

Re: Select case

Thu, 03 Sep 2015 19:38:21 GMT

Thanks a lot,

Set analysis cant replace the nested if?

Re: Select case

sinanozdemir — Thu, 03 Sep 2015 20:01:07 GMT

Depends on where you want to accomplish this, in the front-end or in the back-end (load script)?

Re: Select case

Thu, 03 Sep 2015 20:10:06 GMT

Could you perhaps demonstrate how I would execute this on both scenarios please?

Re: Select case

sinanozdemir — Thu, 03 Sep 2015 20:25:11 GMT

In the front-end since you have case else, then you can put the IF statement within your function or by using set analysis:

Sum(If(MAC_CODE = 963, Amount)) or Sum({<MAC_CODE = {963}>} Amount)

Re: Select case

Thu, 03 Sep 2015 20:44:44 GMT

this is the chart I'm trying to produce:

Rand Value is the sum of that the nested if

Number is the count of the nested if