Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Creator III
Creator III

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

example

 

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

Highlighted
Creator
Creator

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

Highlighted
Creator III
Creator III

can you elaborate? didn't get you
Highlighted
Creator
Creator

I put the below in the script:

script.PNG

And then the field wasn't found

not found.PNG

 

Highlighted
Creator III
Creator III

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

 

Highlighted
Creator III
Creator III

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

Highlighted
Creator II
Creator II

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

Can you cross check and try it again?

Highlighted
Creator
Creator

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

Highlighted
Creator II
Creator II

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