subfield returns only first value in script

[veronicahernand]

Hi,

I have  the following folder with qvds from different years and I need to extract the year (in the *) so I can create a loop for loading the qvds.

But the subfield doesn't work and it only extracts the value for the first row:

Could you help me, please? Thanks!

[Vegar]

It looks correct to me. You could try to skip the  preceding load to see if that is causing your issue

Put the filename() function inside the substring in the original load. Does this improve the result? 

[MayilVahanan]

Hi @veronicahernand 

You can try like below

instead of subfield, you can try with right function also.

right(filename(), 8 )as firstpart

right('pedidos_2018.qvd', 8 ) <-- gives 2018.qvd

[veronicahernand]

It didn't work...

Finally I had to do this:

[Anil_Babu_Samineni]

Why you need another resident load, For me your script should work. Try this for a while and see

Subfield(filename, '_', -1)

[Kushal_Chawda]

@veronicahernand  just one suggestion that use first 1 LOAD when you are loading filename from qvd using "*" so that it will fetch single record only and will not load full QVD data. 

[veronicahernand]

You're absolutely right!!!! I have about 30M rows and the load gets very slow...  thank you!!!!!! 🙂 🙂

[Anil_Babu_Samineni]

If that issue resolved please mark other answer as accepted otherwise please post your answer and close this ticket.

[veronicahernand · Accepted solution]

Hi,

Finally I used this code:

set directory="lib://XXXXXXX";

for each file in filelist('$(directory)\Pedidos_*.qvd')

let año=subfield(subfield(file,'_',3),'.',1);

load 

*

from [lib://YYYYYYY/Pedidos_$(año).qvd]
(qvd);

next file;

but thank you all for your answers!!!!!!!