Case Statement

[#1]

Hi,

Can anyone please help me on the following. I get Syntax error.

eturnReasonsDescription:

LOAD

*,

CASE

WHEN

APPL_REFER_DESC = 'DCI (Debt Consolidation Illustrator)' or APPL_REFER_DESC = 'Appropriate Approval Authority required' or APPL_REFER_DESC = 'Full Lender Report required' or APPL_REFER_DESC = 'NAPS Illustration incorrect' or APPL_REFER_DESC = 'Emergence Period Template not included' or APPL_REFER_DESC = 'Referred to incorrect credit decisioning centre' or APPL_REFER_DESC = 'Pricing' or APPL_REFER_DESC = 'Other Process – see Comment' THEN 'Process'

WHEN

APPL_REFER_DESC = 'PFP' or APPL_REFER_DESC = 'Error in ADV 4' or APPL_REFER_DESC = 'Error in Discounted Cashflows' or APPL_REFER_DESC = 'Demos and/or A&L’s' or APPL_REFER_DESC = 'A&L differs to Inv / Equity' or APPL_REFER_DESC = 'Borrower/ Financial info' or APPL_REFER_DESC = 'Sector Code' or APPL_REFER_DESC = 'AMD (Asset Management Database)' or APPL_REFER_DESC = 'Other Incorrect Info - see Comment' THEN 'Incorrect Info'

WHEN

APPL_REFER_DESC = 'Special Condition Required' or APPL_REFER_DESC = 'Credit Structure Amendment proposed to RM' or APPL_REFER_DESC = 'Further discussion / clarification required' or APPL_REFER_DESC = 'Other Additional Info Required – see Comment' THEN 'Additional Info Required' ,

WHEN

APPL_REFER_DESC = 'Cons not addressed sufficiently' or APPL_REFER_DESC = 'ICB / Arrears / Unpaids' or APPL_REFER_DESC = 'Credit Grading' or APPL_REFER_DESC = 'Tax Confirmation' or APPL_REFER_DESC = 'F&L Checklist for New Money' or APPL_REFER_DESC = 'Exception to Policy not addressed' or APPL_REFER_DESC = 'Stress Testing' or APPL_REFER_DESC = 'Repayment Capacity' or APPL_REFER_DESC = 'Impairment Test Required' or APPL_REFER_DESC = 'Other Credit Assessment Info – see Comment' THEN 'Credit Assessment Information' ,

WHEN

APPL_REFER_DESC = 'SINs incorrect / not attached to NAPS' or APPL_REFER_DESC = 'Security Valuation incorrect' or APPL_REFER_DESC = 'Other Security- see Comment' THEN 'Security'

ELSE

''

END

Return_Reasons

;

SQL

SELECT

"APPL_REFER_DESC",

"APPL_REFER_GRP_CDE",

"APPL_REFER_RSN_CDE",

"CREATED_STAFF_ID",

"CREATED_TMSTMP"

FROM

DDd1P."REASONS_REF";

[Gysbert_Wassenaar]

Qikview doesn't have a CASE - WHEN control statement you can use in a load statement. Either use nested if statements or put your CASE - WHEN stuff in the SQL Select statement. Your source database most likely does understand that syntax.

[#3]

can you please explain both cases how. I tried if statsment in scrip but it says can use max 3 if

[anbu1984]

Case statement in Sql

LOAD * ;

SQL

SELECT

"APPL_REFER_DESC",

"APPL_REFER_GRP_CDE",

"APPL_REFER_RSN_CDE",

"CREATED_STAFF_ID",

"CREATED_TMSTMP",

CASE

WHEN

APPL_REFER_DESC = 'DCI (Debt Consolidation Illustrator)' or APPL_REFER_DESC = 'Appropriate Approval Authority required' orAPPL_REFER_DESC = 'Full Lender Report required' or APPL_REFER_DESC = 'NAPS Illustration incorrect' orAPPL_REFER_DESC = 'Emergence Period Template not included' or APPL_REFER_DESC = 'Referred to incorrect credit decisioning centre' or APPL_REFER_DESC = 'Pricing' or APPL_REFER_DESC = 'Other Process – see Comment' THEN 'Process'

WHEN

APPL_REFER_DESC = 'PFP' or APPL_REFER_DESC = 'Error in ADV 4' or APPL_REFER_DESC = 'Error in Discounted Cashflows'or APPL_REFER_DESC = 'Demos and/or A&L’s' or APPL_REFER_DESC = 'A&L differs to Inv / Equity' or APPL_REFER_DESC ='Borrower/ Financial info' or APPL_REFER_DESC = 'Sector Code' or APPL_REFER_DESC = 'AMD (Asset Management Database)'or APPL_REFER_DESC = 'Other Incorrect Info - see Comment' THEN 'Incorrect Info'

WHEN

APPL_REFER_DESC = 'Special Condition Required' or APPL_REFER_DESC = 'Credit Structure Amendment proposed to RM' orAPPL_REFER_DESC = 'Further discussion / clarification required' or APPL_REFER_DESC = 'Other Additional Info Required – see Comment' THEN 'Additional Info Required' ,

WHEN

APPL_REFER_DESC = 'Cons not addressed sufficiently' or APPL_REFER_DESC = 'ICB / Arrears / Unpaids' orAPPL_REFER_DESC = 'Credit Grading' or APPL_REFER_DESC = 'Tax Confirmation' or APPL_REFER_DESC = 'F&L Checklist for New Money' or APPL_REFER_DESC = 'Exception to Policy not addressed' or APPL_REFER_DESC = 'Stress Testing' orAPPL_REFER_DESC = 'Repayment Capacity' or APPL_REFER_DESC = 'Impairment Test Required' or APPL_REFER_DESC ='Other Credit Assessment Info – see Comment' THEN 'Credit Assessment Information' ,

WHEN

APPL_REFER_DESC = 'SINs incorrect / not attached to NAPS' or APPL_REFER_DESC = 'Security Valuation incorrect' orAPPL_REFER_DESC = 'Other Security- see Comment' THEN 'Security'

ELSE

''

END

Return_Reasons

FROM DDd1P."REASONS_REF";

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

Using If in Qlikview

LOAD *,

If(

APPL_REFER_DESC = 'DCI (Debt Consolidation Illustrator)' or APPL_REFER_DESC = 'Appropriate Approval Authority required' orAPPL_REFER_DESC = 'Full Lender Report required' or APPL_REFER_DESC = 'NAPS Illustration incorrect'orAPPL_REFER_DESC = 'Emergence Period Template not included' or APPL_REFER_DESC = 'Referred to incorrect credit decisioning centre' or APPL_REFER_DESC = 'Pricing' or APPL_REFER_DESC = 'Other Process – see Comment' , 'Process',

If(

APPL_REFER_DESC = 'PFP' or APPL_REFER_DESC = 'Error in ADV 4' or APPL_REFER_DESC = 'Error in Discounted Cashflows'or APPL_REFER_DESC = 'Demos and/or A&L’s' or APPL_REFER_DESC = 'A&L differs to Inv / Equity' orAPPL_REFER_DESC ='Borrower/ Financial info' or APPL_REFER_DESC = 'Sector Code' or APPL_REFER_DESC ='AMD (Asset Management Database)'or APPL_REFER_DESC = 'Other Incorrect Info - see Comment' ,'Incorrect Info',

If(

APPL_REFER_DESC = 'Special Condition Required' or APPL_REFER_DESC = 'Credit Structure Amendment proposed to RM' orAPPL_REFER_DESC = 'Further discussion / clarification required' or APPL_REFER_DESC = 'Other Additional Info Required – see Comment' ,'Additional Info Required' ,

If(

APPL_REFER_DESC = 'Cons not addressed sufficiently' or APPL_REFER_DESC = 'ICB / Arrears / Unpaids'orAPPL_REFER_DESC = 'Credit Grading' or APPL_REFER_DESC = 'Tax Confirmation' or APPL_REFER_DESC = 'F&L Checklist for New Money' or APPL_REFER_DESC = 'Exception to Policy not addressed' or APPL_REFER_DESC = 'Stress Testing' orAPPL_REFER_DESC = 'Repayment Capacity' or APPL_REFER_DESC = 'Impairment Test Required' orAPPL_REFER_DESC ='Other Credit Assessment Info – see Comment' ,'Credit Assessment Information' ,

If(

APPL_REFER_DESC = 'SINs incorrect / not attached to NAPS' or APPL_REFER_DESC = 'Security Valuation incorrect'orAPPL_REFER_DESC = 'Other Security- see Comment' ,'Security','')

)))) As Return_Reasons

;

SQL

SELECT

"APPL_REFER_DESC",

"APPL_REFER_GRP_CDE",

"APPL_REFER_RSN_CDE",

"CREATED_STAFF_ID",

"CREATED_TMSTMP"

FROM DDd1P."REASONS_REF";

[maxgro]

ReturnReasonsDescription:

load

  APPL_REFER_DESC ,

  pick(wildmatch(APPL_REFER_DESC ,

  ''DCI (Debt Consolidation Illustrator)',

  'Appropriate Approval Authority required',

   .....,

   .....,

   ' 'Other Security- see Comment',

   '*'),

  'Process',

  'Process',

   .....,

   .....,

  'Security',

  ''

  );

SQL SELECT

     .....

[#6]

Anbu,

both did gives error

[#7]

Massimo,

can you please explain  the following in your above code please

'*'),

  'Process',

  'Process',

   .....,

   .....,

  'Security',

  ''

[anbu1984]

What is the error message? Error for IF or Case?

[maxgro]

ReturnReasonsDescription:

load

  APPL_REFER_DESC ,

  pick(wildmatch(APPL_REFER_DESC ,

  ''DCI (Debt Consolidation Illustrator)',

  'Appropriate Approval Authority required',

  .....,                                  // replace ...... with a line for every APPL_REFER_DESC value

  .....,

  ' 'Other Security- see Comment',

  '*'),

  'Process',

  'Process',

  .....,                                  // replace ...... with a line for every decod value (Process or IncorrectInfo or...)

  .....,                                  // for the APPL_REFER_DESC

  'Security',

  ''

  );

SQL SELECT

    .....

a small example

[#10]

I used qlikview If and gets attached error