Fetch single values from text, apply to data table.

[#1]

Hi,

Im loading data from a text file.

I've managed to fetch the regular line data, but I also need to add currency and value date to the data table.

So, I need to

1. fetch the currency code and apply It to all of the below lines.

2. fetch the value dates and apply it to the specific amounts. In this case the 6 first value date is 26-Nov and the single at the bottom 28-Nov.

Any Ideas how to approach this?

Sample files attached.

Kind Regards,

Olle

Just an example. Already got the blue, need the two columns marked in red.

[daveamz · Accepted solution]

Hi Olle,

Done! I hope it will work on real data too.

Best regards,

David

[#3]

Wow David, thanks!

This is a true work of art!

I have serveral text files with the exact same format, but for different currencys, do you know how apply multiple file load, something like LOAD *.TXT ? Pls see example files. Same figures but two different currencies. Tried to do it myself but failed.

Really appreciate your help!

Thanks in advance,

Olle

[daveamz]

Hi Olle,

I solved with FOR....EACH...NEXT approach. I also made a folder system for each step: 1 Raw Data, 2 Intermediate Data, 3 Final Data.

When loading currency, the first 3 letters of the row will be used in the WHERE clause, so, I presumed that all files start with 'SAS' (WHERE SubField(@1:n, ' - ', 1) = 'SAS'). Otherwise, you have to figure out how to load only the currency for each file.

Regards,

David

[#5]

Im speechless, thanks a lot. Works perfectly on my real data when applying.

Have a nice weekend!

Regards,

Olle