Forum Discussion
HASONEVALUE help
Use FIRSTNONBLANK instead of VALUES.
Cup_h_prmry = if(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),FIRSTNONBLANK(njrew_h_prmry_outcm[CUPDETAILS]),"")
VALUES returns a column of values, which can't be displayed as a single data point. Using FIRSTNONBLANK gets the first value, depending on the current order of the data.
If you're expecting all of the CUPDETAILS values to be the same within the same CUPCATNO, I would use SELECTEDVALUE instead. This way it doesn't just pick a value randomly based on whatever ordering currently happens to be in place.
Cup_h_prmry = SELECTEDVALUE(njrew_h_prmry_outcm[CUPDETAILS], BLANK())
You can also use MAX, MIN, or any other aggregation function to determine which value to pick out of the list.
In your formula, which is A and which is B?
Apologies..
Column A =
njrew_h_prmry_outcm[CUPCATNO]) andColumn B =njrew_h_prmry_outcm[CUPDETAILS]
To return the first value, you can use
TOPN( 1, VALUES(njrew_h_prmry_outcm[CUPDETAILS]), 1234)
Note the last argument (1234) is where you would normally put your ordering expression. Here, I just chose an arbitrary constant value.
Use FIRSTNONBLANK instead of VALUES.
Cup_h_prmry = if(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),FIRSTNONBLANK(njrew_h_prmry_outcm[CUPDETAILS]),"")
VALUES returns a column of values, which can't be displayed as a single data point. Using FIRSTNONBLANK gets the first value, depending on the current order of the data.
If you're expecting all of the CUPDETAILS values to be the same within the same CUPCATNO, I would use SELECTEDVALUE instead. This way it doesn't just pick a value randomly based on whatever ordering currently happens to be in place.
Cup_h_prmry = SELECTEDVALUE(njrew_h_prmry_outcm[CUPDETAILS], BLANK())
You can also use MAX, MIN, or any other aggregation function to determine which value to pick out of the list.
Hi
i am getting an error on the firstnonblank formula??
Cup_h_prmry = if(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),FIRSTNONBLANK(njrew_h_prmry_outcm[CUPDETAILS]),"")