Best Of

I agree with Mark, for something like this you're better off without regex. I would note, however, that you're also slightly better off just using two SPLIT_PART invocations, like this:

SPLIT_PART(SPLIT_PART(my_string, 'My Prefix', 2), 'My Suffix', 1)

Paired SPLIT_PART invocations like this will give you the part of your string that appears between the two constant strings. So, for our specific case:

SPLIT_PART(SPLIT_PART(body, 'Amount: USD ', 2), ' ', 1)

I'd also recommend using SQUASH_WHITESPACE on the body first thing, so that subtle differences in the text don't cause the process to fail, like multiple spaces appearing in between 'Amount:' and 'USD' or 'USD' and the digits, or perhaps a newline appearing after the digits instead of a space. SQUASH_WHITESPACE will take care of all those cases:

SPLIT_PART(SPLIT_PART(SQUASH_WHITESPACE(body), 'Amount: USD ', 2), ' ', 1)

In Mark's example, this would replace the "amount" formula and the "StringWithAmount" would be unnecessary. Though you could of course split this formula into another named stage if you desired.

All of that said, I am still curious about what was happening that was causing the preview to produce a result while the execution was not. Unfortunately the screenshot does not include enough of the result row to determine whether the failure was happening for the same input value as had been working in the preview. There would be no difference in the regex behavior between the two, so there must have been a difference in the input data the two were working with. Could it be that the Input DataSet tile is set to load only new rows? Just a curiosity.

I see. So some branches and months may not exist and the join won't create rows for missing combinations. Sounds like you need a way to make sure the combinations have all relevant GL accounts, if if they don't exist in the GL Records.

You could handle this by creating a master dataset. Complete with all possible branch, month, and GL account combinations: Branch, Month, Year, GL Account. You could do this by joining a table containing all branches, a date table with years and months, and you chart of account table….which contains all the GL accounts.

After you have a dataset with all possible combinations of branch, month, year, and GL account, perform a Left Join or Right Join (depending on how you set it up) with your actual GL Records table. If a record does not exist, the Amount will be NULL. Handle that as described previously.

Hello @ggenovese,

I wasn't aware that TIME_TO_SEC() functions with text format. I thought it only accepted a Timestamp format. I've verified that it does work. Thanks for the clarification. Yes, this is a much cleaner and easier solution!

there is actually a TIME_TO_SEC() function that will convert to seconds, in order for it to be a decimal you have to determine if you want your value to be a fraction of a minute, an hour, or a day

Thank you so so much! This worked like a charm!

case when `Credit Type 2` is null then 'none' else `Credit Type 2` end

enter this formula into a formula tile in magic etl. this can also be done in a beastmode.