topic Re: Fetch single values from text, apply to data table. in QlikView

Fetch single values from text, apply to data table.

Thu, 27 Nov 2014 14:14:51 GMT

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.

Re: Fetch single values from text, apply to data table.

daveamz — Thu, 27 Nov 2014 16:28:37 GMT

Hi Olle,

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

Best regards,

David

Re: Re: Fetch single values from text, apply to data table.

Fri, 28 Nov 2014 11:11:25 GMT

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

Re: Re: Re: Fetch single values from text, apply to data table.

daveamz — Fri, 28 Nov 2014 12:56:42 GMT

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

Re: Fetch single values from text, apply to data table.

Fri, 28 Nov 2014 13:42:12 GMT

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

Have a nice weekend!

Regards,

Olle