Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
khaycock
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
asinha1991
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)

khaycock
Creator
Creator
Author

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

asinha1991
Creator III
Creator III

can you elaborate? didn't get you
khaycock
Creator
Creator
Author

I put the below in the script:

script.PNG

And then the field wasn't found

not found.PNG

 

arpitkharkia
Creator III
Creator III

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

 

asinha1991
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

imrasyed
Partner - Creator II
Partner - Creator II

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

Can you cross check and try it again?

khaycock
Creator
Creator
Author

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