Jump to content
We've recently updated our Privacy Statement, available here. ×

The value of the column 'COLUMN_NAME' represents the name of the column from which the value should be retrieved in the that row.


arenti
Go to solution Solved by Olivier Keugue Tadaa,

Recommended Posts

The value in the column 'COLUMN_NAME' contains the column name for which the value should be retrieved from that row.

For example, if the row value of the column of 'COLUMN_NAME' is 'PROCESS_CODE', I would like to import the value of the column of 'PROCESS_CODE' as a calculated formula.

It seems to be possible using the $map function and the $esc function.

I tried $map ('['&[COLUMN_NAME]&']','') but failed to generate the calculated column due to an error.

$map ('[PROCESS_CODE]','') or $map('[PROCESS_TIME','') worked properly.

How can I dynamically specify the columns to import the values?

 

PROCESS_CODE, PROCESS_TIME, COLUMN_NAME, TARGET_COMPARE_VALUE

3333, 2024-04-08, PROCESS_CODE, 3333

4444, 2024-04-07, PROCESS_TIME, 2024-04-07

Link to comment
Share on other sites

Hi Arenti,

What is it that you want to achieve? And how are you trying to do that with the $map custom formula?
Normally $map is used in conjunction with Document properties, but it seems like you want to use it with a value from the column COLUMN_NAME.

This custom formula should give you no error: $map("[COLUMN_NAME]",""). But I am not sure what the idea is behind the one you have tried: $map ('['&[COLUMN_NAME]&']','').

Would it be possible to elaborate a bit more on your request and share a sample dxp file?

Kind regards,

David

  • Like 1
Link to comment
Share on other sites

Actually there are 'COLUMN_NAME', 'COMPARE_CRITERIA' and 'COMPARE_VALUE'.

This is data received from another system.

'COLUMN_NAME' stores the column name to be compared, 'COMPARE_CRITERIA' stores the comparison string (ex. >, <, =, etc.), and 'COMPARE_VALUE' stores the value to be compared.

Through the CASE WHEN statement, we want to compare the value of COMPARE_VALUE and the column value corresponding to the value of 'COLUMN_NAME' according to the sign of COMPARE_CRITERIA.

CASE [COMPARE_CRITERIA]
WHEN '=' THEN $map ('['&[COLUMN_NAME]&']','') = [COMPARE_VALUE]
WHEN '>' THEN $map ('['&[COLUMN_NAME]&']','') > [COMPARE_VALUE]
...
END

I would like to check the result for the value set with the formula above.

The value of COLUMN_NAME in the same row is a different column name, and I would like to display the value of that column.

 

 

Ex)

PROCESS_CODE, PROCESS_TIME, COLUMN_NAME, TARGET_COMPARE_VALUE

3333, 2024-04-08, PROCESS_CODE, 3333

4444, 2024-04-07, PROCESS_TIME, 2024-04-07

Link to comment
Share on other sites

Hi Arenti,

Consider this custom column expression for the compare part of the COLUMN_NAME values and the respective columns PROCESS_CODE and PROCESS_TIME:
CASE  WHEN Trim([COLUMN_NAME])=ColumnProperty([PROCESS_CODE],"Name") THEN 'A'
WHEN Trim([COLUMN_NAME])=ColumnProperty([PROCESS_TIME],"Name") THEN 'C'
ELSE '' END

You would need to extend this CASE statement, based upon the COMPARE_CRITERIA and the actual comparison of the values. You could this within the same CASE Statement, but you might also use the above expression as input for the COMPARE_CRITERIA part.

Kind regards,

David

  • Like 1
Link to comment
Share on other sites

I'm really sorry.
I was so bad at explaining.

I would like to get the value of the PROCESS_TIME column when the value of the 'COLUMN_NAME' column is 'PROCESS_TIME' and the value of the PROCESS_CODE column when it is 'PROCESS_CODE'.

The value of The Column 'COLUMN_NAME' is variable.

If I use the column name (like 'PROCESS_CODE') directly, such as '$map ('[PROCESS_CODE]', ''), the value of PROCESS_CODE is normally output.

However, the column name cannot be written directly because the value of the column 'COLUMN_NAME' is variable.

 

I would like to attach the file, but it is not possible to attach it to the outside due to company regulations.

Link to comment
Share on other sites

As in the example below
In the first row, since the value of "COLUMN_NAME" is "PROCESS_CODE", the value of the "PROCESS_CODE" column is outputted and "3333" is displayed
The second row outputs the value of the column 'PROCESS_TIME' because the value of the column 'COLUMN_NAME' is 'PROCESS_TIME'.

 

PROCESS_CODE, PROCESS_TIME, COLUMN_NAME, TARGET_COMPARE_VALUE(I would like this column by calculation column)

3333, 2024-04-08, PROCESS_CODE, 3333 <- Same value of PROCESS_CODE

4444, 2024-04-07, PROCESS_TIME, 2024-04-07 <- Same value of PROCESS_TIME

Link to comment
Share on other sites

Hi Arenti,

Do you mean that in the place of 'A' and 'B' in my example, you would expect the values of the Process_code/Process_time columns, depending on the outcome of the CASE WHEN part?
If so, I guess you want to have these values in one column instead of over 2 columns. Is that correct?

Kind regards,
David

  • Like 1
Link to comment
Share on other sites

Can you clarify what the question is?
To display the column in COLUMN_NAME, the CASE statement works, and this seems to answer the first question.
For instance, in the example attached, with three columns called COLA, COLB and COLC. The case statement is:
 

case  
when [COLNAME]='COLA' then [COLA] 
when [COLNAME]='COLB' then [COLB] 
when [COLNAME]='COLC' then [COLC]
END

And the result is in the screenshot.

image.png.7232ceab7902edd24a4f0458261f6d20.png

test.txt

Link to comment
Share on other sites

Posted (edited)

Yes, this will give me the answer I want, but there are too many columns. about more or less 100.

Is there a function like EVAL() of other systems or programing Language? Is there any way to get the value of the column simpler through value of 'COLUMN_NAME' ?

Edited by arenti
Link to comment
Share on other sites

Hi Arenti,

With just a couple of columns in the list, the proposed CASE statement would do the trick (see attached dxp file).

But with many columns, your CASE statement gets quite long indeed. Not sure if that still performs as expected. We will try to find a way to automate this.

Nonetheless, if you are in need of such a formula for a few columns, I have the dxp attached for your convenience.

Kind regards,

David

COLUMN_NAME.dxp

Link to comment
Share on other sites

Posted (edited)
11 minutes ago, David Boot-Olazabal said:

Hi Arenti,

With just a couple of columns in the list, the proposed CASE statement would do the trick (see attached dxp file).

But with many columns, your CASE statement gets quite long indeed. Not sure if that still performs as expected. We will try to find a way to automate this.

Nonetheless, if you are in need of such a formula for a few columns, I have the dxp attached for your convenience.

Kind regards,

David

COLUMN_NAME.dxp 220.5 kB · 0 downloads

Oops, My spotfire verision is 11.4.5.35... So i can't open the DXP file.

Are you using the CASE WHEN statement as 'Gaia Paolini' answered?

 

I tried to obtain the value of the column through the column name in the form of a string through the $map and $esc functions.


When one column was arbitrarily entered directly, the value of the column was normally fetched.


However, the first parameter is bound to be double-quoted(") text in string format, and errors occur when using functions or values in specific columns.

Edited by arenti
Link to comment
Share on other sites

Hi Arenti,

This is the code I used:

CASE  WHEN Trim([COLUMN_NAME])=ColumnProperty([PROCESS_CODE],"Name") THEN String([PROCESS_CODE])
WHEN Trim([COLUMN_NAME])=ColumnProperty([PROCESS_TIME],"Name") THEN [PROCESS_TIME]
ELSE '' END

It should be similar as Gaia Paolini's.

If you need it for around 100 columns (with a similar number of rows I imagine), this may need a data function.
Can you confirm:
- the number of rows (and do they stay the same)?
- that the column names are always the same?
- are you able to use data functions?

Also, the idea is to add this calculated column after your original set of columns, right?

Kind regards,

David

Link to comment
Share on other sites

Hi arenti,

We think we have found a better solution for your use case, then the CASE statement (or a data function). That solution is to unpivot your data table and add a filter on the COLUMN_NAME column and the original values. You can also add a third transformation, pivoting the outcome back, but that is basically up to you how you want to present the data.

Consider the attached dxp in which you will find the original table on the left hand side. The two tables on the right hand side are the outcome of the transformations that have been done (2Transformations is unpivot+filter; 3Transformations is unpivot+filter+pivot).

Kind regards,

David

COLUMN_NAMES v11.dxp

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...