topic Re: Nested if statement help in QlikView

Nested if statement help

Tue, 24 Nov 2015 02:59:03 GMT

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] ,

I am attempting to add another condition to this statement. If the [Inv_Grp] ,'FHA' then do one of the following;

if(day([K04 Date])<=20,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],3))))),

Is there a way to integrate this into the code??

Re: Nested if statement help

jagan — Tue, 24 Nov 2015 03:11:35 GMT

HI,

Try this

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],

if(day([K04 Date])<=20,

date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],3)))))

)))

as [trial1_due_dt] ,

Regards,

Jagan.

Re: Nested if statement help

jonathandienst — Tue, 24 Nov 2015 06:20:45 GMT

How would you like to do this? The only 'open' slot is the else clause for the last if statement - but at that point [KO4 Date] must be null or empty. AddMonths(null, ..) will only return null.

I have some problems with your current statement - laying it out:

If(len(trim([K04 Date])) <> 0,

//-- KO4 Date is not empty or null --

(

if(day([K04 Date]) <= 15,

date(monthstart(addmonths([K04 Date],1))),

date(monthstart(addmonths([K04 Date],2)))

)

//-- KO4 Date is empty/null --

if([LM Workout Status Act] = 'IN TRIAL',

[K04 Date],

If(len(trim([367])) <> 0,

//-- [367] is not empty or null --

(

if(day([367])<=15,

date(monthstart(addmonths([367],1))),

date(monthstart(addmonths([367],2)))

)

//-- [367] is empty/null (AND [KO4 Date] is also null

if([LM Workout Status Act]='IN TRIAL',

[367]

)

) as [trial1_due_dt],

If [KO4 Date] is null and [LM Workout Status Act] = 'IN TRIAL', then you return [KO4 Date] (which is null). See bold text in your expression. Is that your intention?

The same applies to [367]. If it is null and [LM Workout Status Act] = 'IN TRIAL', then you return [367] (null).

If I were solving this problem, I would draw out a flowchart describing all the possible outcomes and then build the If expression. I would also lay out the expression like the above to see exactly what is happening and confirm that the statement is correct.