topic Re: Creating a new column of data based on 2 fields in QlikView

Creating a new column of data based on 2 fields

beneldridge — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Creating a new column of data based on 2 fields

rubenmarin — Thu, 25 Oct 2018 11:43:42 GMT

Hi Ben, this can be:

LOAD [Request Status],

[Estimated Submission Date],

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

...

Re: Creating a new column of data based on 2 fields

kennethmash — Thu, 25 Oct 2018 11:52:01 GMT

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.

Re: Creating a new column of data based on 2 fields

rubenmarin — Thu, 25 Oct 2018 11:57:18 GMT

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]

Re: Creating a new column of data based on 2 fields

beneldridge — Thu, 25 Oct 2018 12:49:07 GMT

Thanks both, it works.

Much appreciated.

Re: Creating a new column of data based on 2 fields

MarcoWedel — Thu, 25 Oct 2018 20:29:40 GMT

please close your open threads if your questions are answered:

beneldridge

thanks

regards

Marco