IF Statement

[khaycock]

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

[asinha1991]

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]

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

[asinha1991]

can you elaborate? didn't get you

[khaycock]

I put the below in the script:

And then the field wasn't found

[arpitkharkia]

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

[asinha1991]

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]

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

Can you cross check and try it again?

[khaycock]

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]

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