Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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

 

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