I have this code, that runs correctly but I will need some help in the URL:
set ErrorMode=0;
Let vNumSem = Week(today());
Let vNumSem2 = Week(today())-3;
Let vAñoAct = Year(today());
// Como no sabemos cuando actualizarán la URL con la semana, tiramos un par de semanas para atrás y así recogemos las posibles urls
Do while vNumSem2 <= vNumSem
Let vUrlAct = 'https://www.mapa.gob.es/es/estadistica/temas/estadisticas-agrarias/precios_medios_nacionales_$(vAñoAct)s$(vNumSem2)_tcm30-692294.xlsx';
// Hacemos una carga total de lo que hay en la excel, con sus condiciones en el from
// porque como es incremental, no sabemos cuantas columnas tendrá:
Excel_Act:
LOAD
*
FROM [lib://excel web (samca-nt-96_usuario_di033)]
(URL IS [$(vUrlAct)],ooxml, no labels, header is 8 lines, table is [Precios semanales ])
Where Exists(kpos_prod, B & '.' & C);
// Incrementamos la semana para continuar con el bucle
Let vNumSem2 = vNumSem2 + 1;
Loop
So, this has worked fine until some previous days, and that is because, in the URL of the excel, the last number have changed, so this thing:
tcm30-692294.xlsx
There is NO WAY i can know when the web page will change the last number, I thought it was always the same and the only thing that changed, was the week number and year (2024s43) but I already solved that with variables.
I tried rewritting the URL with a * at the end so it gets wathever it is but it doesn´t work as I expected:
Let vUrlAct = 'https://www.mapa.gob.es/es/estadistica/temas/estadisticas-agrarias/precios_medios_nacionales_$(vAñoAct)s$(vNumSem2)_tcm30-*' & '.xlsx';
Is there a way I can solve this problem?? Or should I just wait for it to fail the reload and when I check it, change the number?
You can't use asterisk (*) to point to a file with an unknown name, just like you couldn't use such an address in your web browser. The system (Qlik or the browser) needs to know exactly what you want to fetch.
What you possibly can do, is to find a web page (or API) that lists available file names. From there you could try to extract the exact file name that you want to retrieve. But since you say that there isn't any way to get that information, there's not much you can do until you somehow know the exakt file address.
You can't use asterisk (*) to point to a file with an unknown name, just like you couldn't use such an address in your web browser. The system (Qlik or the browser) needs to know exactly what you want to fetch.
What you possibly can do, is to find a web page (or API) that lists available file names. From there you could try to extract the exact file name that you want to retrieve. But since you say that there isn't any way to get that information, there's not much you can do until you somehow know the exakt file address.
