if statement help

[isciberras]

Hi all,

I'm trying to create a simple if statement but the output is incorrect and I was wondering if you could kindly help.

My desired result is to create a checker to see if things are working the way they should. The logic of the checker is to check if for each period the dates are correct or not. Example if the period is 1 and the SYSTEM_DATE which is a date field  is greater or equal to the date specified below mark it as an issue, otherwise indicate that its fine.

The syntax I am using is:

Reference,
SYSTEM_DATE,
if(PER = 1 and SYSTEM_DATE >= 04/01/2018,'issue',
if(PER = 2 and SYSTEM_DATE >= 01/02/2018,'issue',
if(PER = 3 and SYSTEM_DATE >= 01/03/2018,'issue',
if(PER = 4 and SYSTEM_DATE >= 05/04/2018,'issue',
if(PER = 5 and SYSTEM_DATE >= 03/05/2018,'issue',
if(PER = 6 and SYSTEM_DATE >= 31/05/2018,'issue',
if(PER = 7 and SYSTEM_DATE >= 05/07/2018,'issue',
if(PER = 8 and SYSTEM_DATE >= 02/08/2018,'issue',
if(PER = 9 and SYSTEM_DATE >= 06/09/2018,'issue',
if(PER = 10 and SYSTEM_DATE >= 04/10/2018,'issue',
if(PER = 11 and SYSTEM_DATE >= 01/11/2018,'issue',
if(PER = 12 and SYSTEM_DATE >= 06/12/2018,'issue','fine')))))))))))) as [period checker]
Resident Test_Data;

My result of this is incorrect the logic seems to randomly set some of the dates as an issue and some as fine even though they wont be. Any ideas what I am doing wrong?

[m_woolf]

Try enclosing your dates in apostrophes.

if(PER = 1 and SYSTEM_DATE >= '04/01/2018','issue',
if(PER = 2 and SYSTEM_DATE >= '01/02/2018','issue',

[datagrrl]

What m w said. I tested this and it seems to fix the issue.

if(PER = 1 and SYSTEM_DATE >= '04/01/2018','issue',

if(PER = 2 and SYSTEM_DATE >= '01/02/2018','issue',

if(PER = 3 and SYSTEM_DATE >= '01/03/2018','issue',

The only other issue I could think of is that the default date wasn't formatted correctly.

[lfholland]

If what MW suggests doesn't work, you may need to make sure your system date is in the correct format.  Something like this

if(PER = 1 and DATE(SYSTEM_DATE) >= '04/01/2018','issue',
if(PER = 2 and DATE(SYSTEM_DATE) >= '01/02/2018','issue',

[vishsaggi]

Have not tried may be check at your end if this works?

IF(Match(PER, 1,2,3,4,5,6,7,8,9,10,11,12)>0 AND

        (SYSTEM_DATE >= '04/01/2018' AND SYSTEM_DATE <= 06/12/2018), 'ISSUE', 'Fine') AS [period checker]

[MarcoWedel · Accepted solution]

Hi,

some solutions might be:

      If(PER = 1 and SYSTEM_DATE >= '04/01/2018' or
      PER =  2 and SYSTEM_DATE >= '01/02/2018' or
      PER =  3 and SYSTEM_DATE >= '01/03/2018' or
      PER =  4 and SYSTEM_DATE >= '05/04/2018' or
      PER =  5 and SYSTEM_DATE >= '03/05/2018' or
      PER =  6 and SYSTEM_DATE >= '31/05/2018' or
      PER =  7 and SYSTEM_DATE >= '05/07/2018' or
      PER =  8 and SYSTEM_DATE >= '02/08/2018' or
      PER =  9 and SYSTEM_DATE >= '06/09/2018' or
      PER = 10 and SYSTEM_DATE >= '04/10/2018' or
      PER = 11 and SYSTEM_DATE >= '01/11/2018' or
      PER = 12 and SYSTEM_DATE >= '06/12/2018', 'issue','fine') as [period checker];

or

      If(SYSTEM_DATE >= Pick(PER,'04/01/2018','01/02/2018','01/03/2018',
                                 '05/04/2018','03/05/2018','31/05/2018',
                                 '05/07/2018','02/08/2018','06/09/2018',
                                 '04/10/2018','01/11/2018','06/12/2018'),
        'issue','fine') as [period checker]

or

     If(SYSTEM_DATE >= ApplyMap('mapPERDates',PER),'issue','fine') as [period checker]

using a mapping table like

mapPERDates:
Mapping
LOAD RecNo(),
Date
Inline [
Date
04/01/2018
01/02/2018
01/03/2018
05/04/2018
03/05/2018
31/05/2018
05/07/2018
02/08/2018
06/09/2018
04/10/2018
01/11/2018
06/12/2018   
];

hope this helps

regards

Marco

[isciberras]

Hi Marco,

Thank you so much for your help on this. I tried all the options provided but for some reason the output is still giving me the wrong answer.

When using the applyMap solution I realised that for each SYSTEM_DATE the period checker field gives 2 listings for the same date, one saying issue the other saying its fine. Would you happen to have any ideas as to why this is happening?

[MarcoWedel]

Hi,

please provide some more sample data and your required results to test with.

thanks

regards

Marco

[isciberras]

Thank you for your help Marco with the help of your code the issue is now solved.