Nested if statements and inconsistent data

Mon, 23 Nov 2015 21:39:45 GMT

load
If(len(trim([K04 Date]))<>0 ,
(if(day([K04 Date])<=15,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],2))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 ,
(if(day([367])<=15,date(monthstart(addmonths([367],1))),date(monthstart(addmonths([367],2))))),
if([LM Workout Status Act]='IN TRIAL',[367]
)))) as [trial1_due_dt] ,

In this code, if either a K04 or 367 letter is greater than 0 I want to do one of the following:

If the letter was sent within the first 15 days of the month, increment 1 month and define a[trial1_due_dt]

If the letter was sent after the 15^th, increment 2 months and do the same

Here is what I now need to do. If the Inv_Grp is ‘FHA’ (if (Match( [Inv Grp],'FHA') then do one of the following

If the letter was sent in the first 20 days of the month, increment 1 month, just like above,

If it was sent after the 20^th increment 3 months, instead of 2 months as above.

I have tried several different coding scenarios, the dates are not populating correctly. I end up getting the same dates for both metrics. Here is an example

If(len(trim([K04 Date]))<>0,
(if (not(Match( [Inv Grp],'FHA')),
(if(day([K04 Date])<=15,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],2))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0,
(if (not(Match( [Inv Grp],'FHA')),
(if(day([367])<=15,date(monthstart(addmonths([367],1))),date(monthstart(addmonths([367],2))))),
if([LM Workout Status Act]='IN TRIAL',[367] ,
If(len(trim([K04 Date]))<>0,
(if (Match( [Inv Grp],'FHA'),
(if(day([K04 Date])<=20,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],3))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0,
(if (Match( [Inv Grp],'FHA'),
(if(day([367])<=20,date(monthstart(addmonths([367],1))),date(monthstart(addmonths([367],3))))),
if([LM Workout Status Act]='IN TRIAL',[367]

)))))))))))))))) as [trial1_due_dt] ,

think the issue is my brackets but not sure why it only increments for some entries and not across the entire code. is there a way to streamline this or a best practices when using nested if statements???

Re: Nested if statements and inconsistent data

Clever_Anjos — Tue, 24 Nov 2015 15:30:24 GMT

I would rewrite your logic using preceding load´s calculating intermediary values that would simply your logic

LOAD
   If(FielTmp and MatchFlag, [367_3months]) as [trial1_due_dt]
LOAD
    If(MatchFlag1,[367_3months]) as FielTmp,
    otherlogic;
LOAD
   [367],
   [K04 Date],
   Match( [Inv Grp],'FHA') as MatchFlag1,
   date(monthstart(addmonths([367],3))))) as [367_3months]
from yourqvd.qvd(qvd);

topic Nested if statements and inconsistent data in QlikView

Nested if statements and inconsistent data

Re: Nested if statements and inconsistent data