Get the currency and repeat for each value

Question

Greetings.

I'm working with this test file.

I have imported 3 .csv files. As you can see they all have that  Balance(CURRENCY) at the top of each file.

What I'm trying to do is to get the currency for each one and add it in a new column.

Also deleting the empty rows

The output should be like in the image I'm attaching.

2.PNG

markcurry · Accepted Answer

You can use the Multi-Row formula create a new 'Currency' column, check if column E (presuming it's called [Field 5]) contains a bracket and get a substring to get the 3 digit currency, if it doesn't take the value from the previous currency value....

IF Contains([Field 5],"(") THEN
    SubString([Field 5],8,3)
ELSE
    [Row-1:Currency]
ENDIF

Then you can filter the null rows where the Date field is empty or null.

Aaron_Harter · Accepted Answer

Hi @soncoku,

You can apply some conditional logic to identify the currency for each set of records, then use the Multi-Row Formula tool to fill down the currency value before Filtering out the empty records: