Conditional value labels for missing values

Djtjhin

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. 

 

Thierry_S

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,

TS