Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!!!!!!!
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?
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
It didn't work...
Finally I had to do this:
Why you need another resident load, For me your script should work. Try this for a while and see
Subfield(filename, '_', -1)
@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.
You're absolutely right!!!! I have about 30M rows and the load gets very slow... thank you!!!!!! 🙂 🙂
If that issue resolved please mark other answer as accepted otherwise please post your answer and close this ticket.
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!!!!!!!