Select case

[#1]

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

[sinanozdemir]

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

[#3]

Thanks a lot, i will try it out now

[sinanozdemir]

Yeah, sure.

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

[#5]

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

[sinanozdemir]

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

[#7]

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?

[sinanozdemir]

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.

[#9]

Thanks a lot,

Set analysis cant replace the nested if?

[sinanozdemir · Accepted solution]

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