Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
khaycock
Contributor

IF Statement

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).

control.png

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

Labels (1)
9 Replies
asinha1991
Contributor III

Re: IF Statement

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

example

 

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

khaycock
Contributor

Re: IF Statement

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

asinha1991
Contributor III

Re: IF Statement

can you elaborate? didn't get you
khaycock
Contributor

Re: IF Statement

I put the below in the script:

script.PNG

And then the field wasn't found

not found.PNG

 

arpitkharkia
Contributor III

Re: IF Statement

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

 

asinha1991
Contributor III

Re: IF Statement

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

imrasyed
Contributor

Re: IF Statement

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

Can you cross check and try it again?

khaycock
Contributor

Re: IF Statement

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

Marcos_rv
Contributor II

Re: IF Statement

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