Creating a new column of data based on 2 fields

[beneldridge]

Hi all,

I am trying to create a "no show" column based on 2 fields of data:

If the status of a request is reserved and the Estimated Submission Date has passed I would like a column (No Show) to highlight this as yes.

Request Status	Estimated Submission Date	No Show
Reserved	24/10/2018	Yes
Reserved	26/10/2018	(Blank)
Reserved	23/10/2018	Yes
In-Progress	20/10/2018	(Blank)
Closed	20/10/2018	(Blank)
Reserved	24/10/2018	Yes

Any help would be greatly appreciated.

Thanks,

Ben

[rubenmarin]

Hi Ben, this can be:

LOAD [Request Status],

     [Estimated Submission Date],

     If([Estimated Submission Date]<Today(), 'Yes') as [No Show]

...

[kennethmash]

Just to add to Ruben's solution I think what you need is something like this:

If([Estimated Submission Date]<Today() AND [Request Status] = 'Reserved', 'Yes', 'No') as [No Show]

This satisfies both conditions. I also find it useful to put actual values in place of null values. This allows you to select the null values on the front-end which can be useful if for example someone was interested in records that don't meet that criteria or if you want to conduct some sort of data cleanup exercise.

[rubenmarin]

You are right, I forgot that condition.

But I would remove the 'No' part to keep (Blank) the else part, or should it tbe the text '(Blank)'?

If([Estimated Submission Date]<Today() AND [Request Status] = 'Reserved', 'Yes') as [No Show]

[beneldridge]

Thanks both, it works.

Much appreciated.

[MarcoWedel]

please close your open threads if your questions are answered:

beneldridge

thanks

regards

Marco