topic Re: IF Statement in QlikView

IF Statement

khaycock — Sat, 16 Nov 2024 03:49:49 GMT

I am trying to create a field that shows three different results: 'Is Reporting', 'Not Reporting' and 'N/A'.

I currently have the following three fields set up, however my end goal is to achieve a combination of ControlCredant and CredentReporting. I want CredentReporting to show 'N/A' when ControlCredant says 'N/A' (or when ControlCredentDate is null).

This is my current set up in the script:

Stage 1:

if(ControlCredant='','N/A',ControlCredant) AS ControlCredant,

Stage 2:

date(floor(date#(ControlCredant,'YYYY-MM-DD hh:mm:ssZ')),'DD/MM/YYYY') AS ControlCredantDate,

For some reason the IF statement in Stage 3 doesn’t work correctly with the current date format so I have to force it to a different one.

Stage 3:

IF(ControlCredantDate<>'',if(date(today())-15 >= ControlCredantDate, 'Not Reporting', 'Is Reporting'),'N/A') AS CredentReporting,

I would expect stage 3 to work and give the N/A if it is blank as the other IF statement says what to do when it is blank, but it does not. I’m then stuck with three fields when all I want is two. One with a date and one with a correct Reporting field..

Have I missed something here? It seems something really simple but the IF statement just won't work properly

Re: IF Statement

asinha1991 — Wed, 17 Apr 2019 09:19:10 GMT

can you add or and check for null value wherever you are checking for blank?

example

if(ControlCredant='' or isnull(ControlCredant),'N/A',ControlCredant)

Re: IF Statement

khaycock — Wed, 17 Apr 2019 09:33:12 GMT

When adding the IsNull, the field then isn't found completely when i run the script 😞

Re: IF Statement

asinha1991 — Wed, 17 Apr 2019 09:58:26 GMT

can you elaborate? didn't get you

Re: IF Statement

khaycock — Wed, 17 Apr 2019 10:02:50 GMT

I put the below in the script:

And then the field wasn't found

Re: IF Statement

arpitkharkia — Wed, 17 Apr 2019 10:19:54 GMT

It will be great if you can post some sample data and expected results. 🙂

Re: IF Statement

asinha1991 — Wed, 17 Apr 2019 10:50:08 GMT

that's strange but it should not be because of isnull, can you just load the first table and check if the field is there

Re: IF Statement

imrasyed — Wed, 17 Apr 2019 11:14:30 GMT

I can see the two different columns ControlCredent and ControlCredant in the attached screenshot.

Can you cross check and try it again?

Re: IF Statement

khaycock — Wed, 17 Apr 2019 13:40:28 GMT

I loaded just the table in a separate .qvw and it ran through successfully. However I don't understand how this helps me achieve the finished result.

Do I then need to amend the final IF statement? I've attached the .qvw to help make the script part a bit easier because it's a bit ugly ha

Re: IF Statement

Marcos_rv — Wed, 17 Apr 2019 13:56:36 GMT

Try this:

MY_TABLE:

//load precedent

LOAD *,
IF(ControlCredantDate<>'N/A', if(date(today())-15 >= ControlCredantDate, 'Not Reporting', 'Is Reporting'),'N/A') AS CredentReporting;

LOAD *,
if( isnull(ControlCredant) or (ControlCredant=''),'N/A',ControlCredant) AS ControlCredant,
if( isnull(ControlCredant) or (ControlCredant=''),'N/A',date(floor(date#(ControlCredant,'YYYY-MM-DD hh:mm:ss')),'DD/MM/YYYY')) AS ControlCredantDate
FROM .....QVD(QVD);