Value extraction and add to new column

[Baz123]

Hi,

I have a data set that has a preformatted workbook. I have managed to setup a workflow that dynamically extracts the table required, but I have some more work left to get it formatted for end use.

The table looks like the below:

ID	Compare
SHT APR >90M <150M
SHT APR <100K

What I need to do is extract the value from the ID column (the second value = upper limit in the case with a range of values as seen in the first row) and then add the value to the Compare column all the way down to the next ID so it would look like this:

ID	Compare
SHT APR >90M <150M	150,000,000
	150,000,000
SHT APR <100K	100,000
	100,000
	100,000
	100,000

I cant seem to figure out how to extract the value if there is a range, and then convert that to the numerical value.

Late edit / update - ID values are longer than shown. Should look like the above, but with extra data on end of string like this :

SHT APR >90M <150M ABC DEF GHI

Thanks.

[ShankerV]

Hi @Baz123 

One way of doing this.

Many thanks

Shanker V

[ShankerV]

Hi @Baz123 

Step 1: Input tool

Step 2: 

\s.(\d+)(\u)$

Step 3:

IF [RegExOut2]="M"
THEN [Compare]*1000000
ELSE [Compare]*1000
ENDIF

Step 4:

IF Isnull([Compare])
THEN [Row-1:Compare]
ELSE [Compare]
ENDIF

Many thanks

Shanker V

[flying008]

Hi, @Baz123 

Another way for you:

ToNumber(iif(IsEmpty([ID]),[Row-1:Compare],IIF(Right([ID], 1) in ('M','k'),Replace(Right([ID], Length([ID]) - FindString([ID], '<') -1), Right([ID], 1), Switch(Right([ID], 1),Null(),'M','000000','k','000')), Right([ID], Length([ID]) - FindString([ID], '<') -1))))

Input		Output
ID		ID	Compare
SHT APR >90M <150M		SHT APR >90M <150M	150,000,000
			150,000,000
SHT APR <100K		SHT APR <100K	100,000
			100,000
			100,000
			100,000

[Baz123]

Hi @ShankerV - I will see if I can get it to work

@flying008 - The formula seems to work, however I forgot to add in that the ID values have extra characters on the end of the string so this:

SHT APR >90M <150M

Is actually this

SHT APR >90M <150M ABC DEF GHI

Can your expression be modified to trim the string down? It looks like Alteryx is throwing a conversion error because of that.

Thanks.

[Raj]

By using the regular expression pattern <(\d+(?:M|K)), you can extract the value that follows the "<" symbol and consists of one or more digits followed by either "M" or "K".

I think you will be able to do the remaining part
Hope this helps.

[Raj]

use this afterwords
If Contains([RegExOut1],"M") then replace([RegExOut1],"M",",000,000") elseif Contains([RegExOut1],"K") then replace([RegExOut1],"K",",000") elseif [RegExOut1]= null() then [Row-1:RegExOut1] else "0" endif

[flying008 · Accepted solution]

Hi, @Baz123 

Please try this Multi-Row formula:

ToNumber(iif(IsEmpty([ID]),[Row-1:Compare],IIF(Right(REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'), 1) in ('M','K'),Replace(REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'), Right(REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'), 1), Switch(Right(REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'), 1),Null(),'M','000000','K','000')), REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'))))

BTW, if you want use simple formula, then can flow 2 steps:

Spoiler

1- 

<(\d+[Mk]?)

2- 

ToNumber(iif(IsEmpty([ID]),[Row-1:Compare],Replace([ID], Right([ID],1), Switch(Right([ID],1), Right([ID],1), 'M','000000','K','000'))))

[Baz123]

Thanks @flying008 - I used the last method here in combination with my other steps to get what I needed.

Appreciate the help!