Hi I have following data , expected result I'm looking for last value of the column, I'm looking for optimized workflow SL NOColumn-1Column-2Column-3Column-4Column-5100ABCDE101SDG 102FRAH 103GJW 104SG 105MG Expected Output: SL NOColkumn-1Column-2Column-3Column-4Column-5Last Value100ABCDEE101SDG G102FRAH H103GJW W104SG G105MG G

Hi, @BRRLL99 There are 2 ways to get it . 1- You only need change the filter condition from !IsEmpty([Value]) to REGEX_Match([Name], '.+\d$') && !IsEmpty([Value]) 2- You only need use Select tool or Dynamic Select tool to filter useless fields before transpose tool.

Last Column Value

BRRLL99

I have following data , expected result I'm looking for last value of the column, I'm looking for optimized workflow

SL NO	Column-1	Column-2	Column-3	Column-4	Column-5
100	A	B	C	D	E
101	S	D	G
102	F	R	A	H
103	G	J	W
104	S	G
105	M	G

Expected Output:

SL NO	Colkumn-1	Column-2	Column-3	Column-4	Column-5	Last Value
100	A	B	C	D	E	E
101	S	D	G			G
102	F	R	A	H		H
103	G	J	W			W
104	S	G				G
105	M	G				G

Data Investigation

Accepted answers

DataNath

@BRRLL99 if they're comma separated like in your example then there's a few ways you can do this, for example - string functions:

Right([List],
FindString(
ReverseString([List]),','))

RegEx:

REGEX_Replace([List], '.+,(.+)', '$1')

Just replace [List] with your [Concat_Value] field.

flying008

Hi, @BRRLL99

There is a easy way for you want:

录制_2023_04_14_11_04_16_340.gif

flying008

Hi, @BRRLL99

There are 2 ways to get it .

1- You only need change the filter condition from !IsEmpty([Value]) to REGEX_Match([Name], '.+\d$') && !IsEmpty([Value])

录制_2023_04_14_13_44_39_786.gif

2- You only need use Select tool or Dynamic Select tool to filter useless fields before transpose tool.

录制_2023_04_14_13_28_02_128.gif

BRRLL99

please share the file

DataNath

Hey @BRRLL99, just need to change the Formula expression in my original workflow so it only takes the last letter:

Find last value in multiple fields.yxmd

Christina_H

Try this

Last value.yxmd

RobertOdera

Sure thing, @BRRLL99

Please see below.

I hope you find it helpful - Cheers!

For_BRRLL99_v2.yxzp

All comments

DataNath

Hey @BRRLL99, here's one way I'd go about this:

We first Transpose the data to get all values alongside the [SL NO]:

We then make a concatenated list - values Transpose in order and then Summarize in order of appearance:

A simple Formula expression gets us the rightmost value i.e. the last in the concatenated list:

After that we just Join back and sort! Hope this helps.

Find last value in multiple fields.yxmd

BRRLL99

I have concatenated the data using summarize

how can i pick the last value dynamically ( without using text to columns tool)

original data has been made few changes

DataNath

@BRRLL99 if they're comma separated like in your example then there's a few ways you can do this, for example - string functions:

Right([List],
FindString(
ReverseString([List]),','))

RegEx:

REGEX_Replace([List], '.+,(.+)', '$1')

Just replace [List] with your [Concat_Value] field.

RobertOdera

Hi, @BRRLL99

Kindly consider the below.

I hope you find it helpful - Cheers!

For_BRRLL99.yxzp

flying008

Hi, @BRRLL99

There is a easy way for you want:

录制_2023_04_14_11_04_16_340.gif

BRRLL99

@DataNath @flying008 @RobertOdera

there has been a update my data, I have 15 columns in similar way, my end goal is to pick last column value

expected output:

SL NO	COLUMN -1	COLUMN -1 Q	COLUMN -2	COLUMN - 2 Q	COLUMN -3	COLUMN - 3 Q	COLUMN -4	COLUMN - 4 Q	COLUMN -5	COLUMN -5 Q	Last column Value
100	A	12	B	56	C	90	D	34	E	12	E
101	S	15	D	4	G	100					G
102	F	99	R	34	A	120	H	56			H
103	G	54	J	77	W	88					W
104	S	34	G	44							G
105	M	19	G	55							G

INPUT:

SL NO	COLUMN -1	COLUMN -1 Q	COLUMN -2	COLUMN - 2 Q	COLUMN -3	COLUMN - 3 Q	COLUMN -4	COLUMN - 4 Q	COLUMN -5	COLUMN -5 Q
100	A	12	B	56	C	90	D	34	E	12
101	S	15	D	4	G	100
102	F	99	R	34	A	120	H	56
103	G	54	J	77	W	88
104	S	34	G	44
105	M	19	G	55