topic if statement help in QlikView

if statement help

isciberras — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: if statement help

m_woolf — Mon, 13 Aug 2018 14:09:12 GMT

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

Re: if statement help

datagrrl — Mon, 13 Aug 2018 14:12:10 GMT

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.

Re: if statement help

lfholland — Mon, 13 Aug 2018 14:19:42 GMT

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

Re: if statement help

vishsaggi — Mon, 13 Aug 2018 15:02:29 GMT

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]

Re: if statement help

MarcoWedel — Mon, 13 Aug 2018 21:07:29 GMT

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

      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]

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

Re: if statement help

isciberras — Wed, 15 Aug 2018 09:07:41 GMT

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?

Re: if statement help

MarcoWedel — Wed, 15 Aug 2018 20:28:43 GMT

Hi,

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

thanks

regards

Marco

Re: if statement help

isciberras — Thu, 16 Aug 2018 15:38:33 GMT

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