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]

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

[David Boot-Olazabal]

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

[arenti]

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

[David Boot-Olazabal]

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

[arenti]

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.

[arenti]

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

[David Boot-Olazabal]

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

[arenti]

I would like to display two or more columns in one column through a column calculated by the value of the column name listed in the column 'COLUMN_NAME'.

[arenti]

Through the calculated column, the values of two or more column names described in the column 'COLUMN_NAME' are to be displayed in one column.

[Gaia Paolini]

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.

test.txt

[arenti]

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 April 9 by arenti

[David Boot-Olazabal]

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

[arenti]

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 April 9 by arenti

[David Boot-Olazabal]

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

[David Boot-Olazabal]

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

[Olivier Keugue Tadaa]

Hi Arenti,
I hope this can help !!