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:
if(ControlCredant='','N/A',ControlCredant) AS ControlCredant,
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.
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
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
IF(ControlCredantDate<>'N/A', if(date(today())-15 >= ControlCredantDate, 'Not Reporting', 'Is Reporting'),'N/A') AS CredentReporting;
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