topic Conditional value labels for missing values in Discussions

Conditional value labels for missing values

Djtjhin — Sat, 10 Jun 2023 23:31:37 GMT

I wonder if there's a way to set up conditional value labels based on values from two columns. If I refer to the snapshot table below, I have "column 1" which could either be filled or empty and I also have "column 2" which either be filled or empty. What I'm trying to do is for "column 2" to be formatted like "Column 3".

Which is: (i) values formatted to a date format if there's a value AND the "column 1" value on the same row is not empty, (ii) To display "Not Credible" if the "Column 2" value is missing but "Column 1" value on the same row is not empty, and (iii) to remain as "." or empty if both "column 1" & "column 2" values on the same row are missing.

Appreciate the help.

Re: Conditional value labels for missing values

Thierry_S — Fri, 11 Jun 2021 06:50:20 GMT

Hi,

The task you presented to us is a bit tricky because you wont both text "Not Credible" and Dates in the same column. Thus, assuming that your Column 2 dates are entered as numbers (no leading "0" for month < 10) and that the order of the day - month - year is always the same, here is a formula that should do the trick:

DATEIN = Char( :Column 2 );
DATEOUT = "";
If( !Is Missing( :Column 1 ),
	If( !Is Missing( :Column 2 ) & Is Number( :Column 2 ),
		If(
			Length( DATEIN ) == 8,
				DATEOUT = Format( Informat( DATEIN, "mmddyyyy" ), "ddMonyyyy" ),
			Length( Char( DATEIN ) ) == 7,
				DATEIN = "0" || DATEIN;
				DATEOUT = Format( Informat( DATEIN, "mmddyyy" ), "ddMonyyyy" );
		)
	,
		DATEOUT = "Not Credible"
	)
,
	DATEOUT = "."
);
DATEOUT;

I intentionally used local variable to make the formula more readable but it is not really required.

Let us know if that works for you.

Best,

Re: Conditional value labels for missing values

Djtjhin — Fri, 11 Jun 2021 07:23:38 GMT

Thanks @Thierry_S. If I understand correctly, this would make the "column 2" column type to be "character", right ?

What if I want to keep it as Numeric for further analysis ? The whole idea of differentiating blank and "not credible" is just for filtering purposes. I know I can make a new column for that but I wonder if there are other ways.

Re: Conditional value labels for missing values

Thierry_S — Fri, 11 Jun 2021 14:08:54 GMT

Hi,
You do not need to transform Column 2 into character: I took care of it in the formula (see: Char (Column 2) statement in the formula).
Did you try to use the formula I shared and did it yield the results you expected?
Let me know if you need help with the implementation of this potential solution.
Best,
TS

Re: Conditional value labels for missing values

Djtjhin — Fri, 11 Jun 2021 14:47:32 GMT

I tried to paste the formula you provided into "Column 2" column formula but it gave me an error. Perhaps I misunderstood your guidance.

Re: Conditional value labels for missing values

Thierry_S — Fri, 11 Jun 2021 15:38:28 GMT

Hi,
Sorry for the confusion: the formula is intended to be added to a third column and then referring to Column 1 and Column 2; does that make sense?
Best,
TS