Nested if statement

[#1]

Calendar:

LOAD hash128(TransactionDATE) AS TransactionDATEKey

,Date(Monthstart(DATE#(TransactionDATE,'YYYYMM')), 'MM') as MonthNumber

,Year(DATE#(TransactionDATE,'YYYYMM')) as Year

,DATE#(TransactionDATE,'YYYYMM') as YearMonth

,Month(DATE#(TransactionDATE,'YYYYMM')) as Month

,Days

,IF(Year(DATE#(TransactionDATE,'YYYYMM')) = '2008', 1, 0) as PPYFlag

,IF(Year(DATE#(TransactionDATE,'YYYYMM')) = '2009', 1, 0) as PYFlag

,IF(Year(DATE#(TransactionDATE,'YYYYMM')) = '2010', 1, 0) as CYFlag

,'Q' & Ceil(Month(DATE#(TransactionDATE,'YYYYMM'))/3) as Quarter

,IF((num#(Date(Monthstart(DATE#(TransactionDATE,'YYYYMM')), 'MM'),'#') > '0' and num#(Date(Monthstart(DATE#(TransactionDATE,'YYYYMM')), 'MM'),'#') <= '12')

,Month(DATE#(TransactionDATE,'YYYYMM')), '') as CYYearMonth

,if(TransactionDATE = '200702', '28',

if(TransactionDATE = '200802', '29',

if(TransactionDATE = '200902', '28',

if(TransactionDATE = '201002', '28',

if(right(TransactionDATE,2) = '01', '31',

if(right(TransactionDATE,2) = '03', '31',

if(right(TransactionDATE,2) = '05', '31',

if(right(TransactionDATE,2) = '07', '31',

if(right(TransactionDATE,2) = '08', '31',

if(right(TransactionDATE,2) = '10', '31',

if(right(TransactionDATE,2) = '12', '31',

if(right(TransactionDATE,2) = '04', '30',

if(right(TransactionDATE,2) = '06', '30',

if(right(TransactionDATE,2) = '09', '30',

if(right(TransactionDATE,2) = '11', '30', right(TransactionDATE,2))))))))))))))))) as Days

from $(zqvdpath) TransactionsQVD.qvd (qvd);

My nested if statement is giving errors... It is saying:

and various other errors.. i have changed it around so much that i do not know where i started 😞

Please could someone help me out or point out what i'm doing wrong.

Thanks

[Miguel_Angel_Baeyens · Accepted solution]

Hello Richard,

Rather than checking that error and check one by one every parentheses pair in your nested if, why don't you use a map? Syntax would be something like

DaysMap:MAPPING LOAD * INLINE [ TransactionDate, Day 200902, 28 201002, 28 01, 31]; Calendar:LOAD ... ApplyMap('DaysMap', right(TransactionDATE, 2)) AS Days

It's easier to debug and clearer to work with.

It seems that you are trying to get correct number of total days in a month, so, another suggestion is

Day(MonthEnd(TransactionDATE)) AS Days

Regards.

[#3]

There is one too mach closing parenthesis. Last one must be eraised.

[#4]

if(TransactionDATE = '200702', '28', if(TransactionDATE = '200802', '29', if(TransactionDATE = '200902', '28', if(TransactionDATE = '201002', '28', if(right(TransactionDATE,2) = '01', '31', if(right(TransactionDATE,2) = '03', '31', if(right(TransactionDATE,2) = '05', '31', if(right(TransactionDATE,2) = '07', '31', if(right(TransactionDATE,2) = '08', '31', if(right(TransactionDATE,2) = '10', '31', if(right(TransactionDATE,2) = '12', '31', if(right(TransactionDATE,2) = '04', '30', if(right(TransactionDATE,2) = '06', '30', if(right(TransactionDATE,2) = '09', '30', if(right(TransactionDATE,2) = '11', '30', right(TransactionDATE,2)))))))))))))))) as Days

[#5]

Excellent, thanks Miguel.. Knew there was an easier way of doing it.

Thanks

[Stefan_Walther]

Hi,

generally speaking it would be better to avoid a nested if scenario in your case (as already suggested by other comments).

But if you are still struggling with the nested ifs, maybe you want to have a look at

http://www.qlikblog.at/464/tool-creating-nested-ifstatements/

Regards, Stefan