topic Re: Nested If Statements in QlikView

Nested If Statements

Anonymous — Mon, 11 Jan 2016 20:24:55 GMT

Hello,

I am looking to create a field based on two other fields using nested if statements.

LTV Pct	20 years of less	20.01 years or more
>95%	6	13
90.01-95%	5	12
85.01-90%	4	10
85% or less	3	6

The first field is LTV Pct and the second field is Loan Term Actual Mths (i.e. 20 years or less / 20.01 years or more).

Based on these two fields using the table above, I want to assign values to a new field titled "Years".

EXAMPLE:

If [LTV Pct] >95% AND [Loan Term Actual Mths] >20.01, [Years] = 13.

Please let me know if you have any questions. Thank you in advance!

Re: Nested If Statements

sunny_talwar — Mon, 11 Jan 2016 20:42:50 GMT

Not sure how the data is laid out but a nested if statement can work

If([LTV Pct] <= 0.85,

If([Loan Term Actual Mths] > 20.01, 6, 3),

If([LTV Pct] <= 0.90,

If([Loan Term Actual Mths] > 20.01, 10, 4),

If([LTV Pct] <= 0.95,

If([Loan Term Actual Mths] > 20.01, 12, 5),

If([LTV Pct] > 0.95,

If([Loan Term Actual Mths] > 20.01, 13, 6))))) as Years

Re: Nested If Statements

Anonymous — Mon, 11 Jan 2016 21:05:13 GMT

Hi Sunny, thank you so much!

I agree this logic would work, however, I am getting an error message that 'Loan Term Actual Mths' is not being recognized in the if statement lines.

Any ideas why?

Re: Nested If Statements

sunny_talwar — Mon, 11 Jan 2016 21:43:02 GMT

I have assumed that you have a field named Loan Team Actual Mths in your database (or is getting created somewhere before the if statement. If the field isn't there, you will see an error message. Can you share your script?

Re: Nested If Statements

Anonymous — Mon, 11 Jan 2016 21:47:36 GMT

I actually created a field [Loan Term Actual Years], which converts months to years. Here is my script below:

[LTV Pct],

[Base Loan Amt],

[Current Loan Amt],

[Loan Term Actual Mths],

[Loan Term Actual Mths]/12 as [Loan Term Actual Years],

If([LTV Pct] <= 0.85,If([Loan Term Actual Years] > 20.01, 6, 3),

If([LTV Pct] <= 0.90,If([Loan Term Actual Years] > 20.01, 10, 4),

If([LTV Pct] <= 0.95,If([Loan Term Actual Years] > 20.01, 12, 5),

If([LTV Pct] > 0.95,If([Loan Term Actual Years] > 20.01, 13, 6))))) as Years,

Re: Nested If Statements

sunny_talwar — Mon, 11 Jan 2016 22:05:32 GMT

Try this:

LOAD *,

If([LTV Pct] <= 0.85,If([Loan Term Actual Years] > 20.01, 6, 3),

If([LTV Pct] <= 0.90,If([Loan Term Actual Years] > 20.01, 10, 4),

If([LTV Pct] <= 0.95,If([Loan Term Actual Years] > 20.01, 12, 5),

If([LTV Pct] > 0.95,If([Loan Term Actual Years] > 20.01, 13, 6))))) as Years;

LOAD [LTV Pct],

[Base Loan Amt],

[Current Loan Amt],

[Loan Term Actual Mths],

[Loan Term Actual Mths]/12 as [Loan Term Actual Years],

The above set up is called 'Preceding Load'. The Field Loan Term Actual Years is not available in the preceding load and can now be referred to create Years.

Re: Nested If Statements

Anonymous — Mon, 11 Jan 2016 23:24:04 GMT

This appears to be working. Thank you very much!