topic Re: Case Statement in QlikView

Case Statement

Mon, 09 Mar 2015 15:33:29 GMT

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";

Re: Case Statement

Gysbert_Wassenaar — Mon, 09 Mar 2015 15:37:50 GMT

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.

Re: Case Statement

Mon, 09 Mar 2015 15:42:11 GMT

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

Re: Case Statement

anbu1984 — Mon, 09 Mar 2015 16:02:25 GMT

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";

Re: Case Statement

maxgro — Mon, 09 Mar 2015 19:38:18 GMT

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

.....

Re: Case Statement

Tue, 10 Mar 2015 09:41:17 GMT

Anbu,

both did gives error

Re: Case Statement

Tue, 10 Mar 2015 09:53:12 GMT

Massimo,

can you please explain the following in your above code please

'*'),

'Process',

'Process',

.....,

'Security',

Re: Case Statement

anbu1984 — Tue, 10 Mar 2015 10:15:57 GMT

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

Re: Case Statement

maxgro — Tue, 10 Mar 2015 10:54:39 GMT

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

Re: Case Statement

Tue, 10 Mar 2015 11:33:37 GMT

I used qlikview If and gets attached error

Re: Case Statement

Tue, 10 Mar 2015 12:23:25 GMT

Massimo thanks but i used the following it's gives me only dashes:

ReturnReasonsDescription:

LOAD

APPL_REFER_DESC,

pick

(wildmatch('DCI (Debt Consolidation Illustrator)','Appropriate Approval Authority required',

'Full Lender Report required'

,'NAPS Illustration incorrect','Emergence Period Template not included',

'Referred to incorrect credit decisioning centre'

,'Pricing','Other Process – see Comment','PFP','Error in ADV 4',

'Error in Discounted Cashflows'

,'Demos and/or A&L’s','A&L differs to Inv / Equity','Borrower/ Financial info','Sector Code',

'AMD (Asset Management Database)'

,'Other Incorrect Info - see Comment','Special Condition Required',

'Credit Structure Amendment proposed to RM'

,'Further discussion / clarification required',

'Other Additional Info Required – see Comment'

,'Cons not addressed sufficiently','ICB / Arrears / Unpaids',

'Credit Grading'

,'Tax Confirmation','F&L Checklist for New Money','Exception to Policy not addressed','Stress Testing',

'Repayment Capacity'

,'Impairment Test Required','Other Credit Assessment Info – see Comment',

'SINs incorrect / not attached to NAPS'

,'Security Valuation incorrect','Other Security- see Comment','*'),'Process','Process','Process',

'Process'

,'Process','Process','Process','Process','Incorrect Info','Incorrect Info','Incorrect Info','Incorrect Info',

'Incorrect Info'

,'Incorrect Info','Incorrect Info','Incorrect Info','Incorrect Info','Additional Info Required',

'Additional Info Required'

,'Additional Info Required','Additional Info Required','Credit Assessment Information',

'Credit Assessment Information'

,'Credit Assessment Information','Credit Assessment Information','Credit Assessment Information',

'Credit Assessment Information'

,'Credit Assessment Information','Credit Assessment Information','Credit Assessment Information',

'Credit Assessment Information'

,'Security','Security','Security','unknown')

;

SQL

SELECT

"APPL_REFER_DESC",

"APPL_REFER_GRP_CDE",

"APPL_REFER_RSN_CDE",

"CREATED_STAFF_ID",

"CREATED_TMSTMP"

Re: Case Statement

maxgro — Tue, 10 Mar 2015 12:33:53 GMT

It seems you miss the field APPL_REFER_DESC after the wildmatch(

And start simple, with 2 or 3 values, when the expression works, add the other

Re: Case Statement

anbu1984 — Tue, 10 Mar 2015 13:27:11 GMT

Error was due to no space before and after "Or". Try this

LOAD *,

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' 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' ,'Incorrect Info',

If(

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' ,'Credit Assessment Information' ,

If(

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

)))) As Return_Reasons

;

Re: Case Statement

Thu, 19 Mar 2015 10:29:06 GMT

thanks Anbu