Exclude last table row inside a looping

[heitorcarlosgomes]

Hello everyone,

I've encountered an issue with my code. Currently, I have a loop set up to load all files from within multiple folders. However, each of my tables contains additional information in the last row that I would like to exclude from my database. I'm not sure how to proceed.

Please note that I need to delete the last row of the table before loading the next spreadsheet.

Tabela:
LOAD * Inline [
Mês, Ano, ESTADO, MUNICIPIO, FABRICANTE, PRODUTO, TOTAL
];

For Each Pasta in DirList('lib://Mkt-AUT/Implementos Rodoviários/*')
For Each Arquivo in FileList('$(Pasta)/*')
Concatenate(Tabela) // Carrega em uma tabela temporária
Lista_Nome_Arquivo:
LOAD
SubField(FileBaseName('$(Arquivo)'), ' ', 1) AS Mês,
SubField(FileBaseName('$(Arquivo)'), ' ', 2) AS Ano,
ESTADO,
MUNICIPIO,
FABRICANTE,
PRODUTO,
TOTAL
FROM [$(Arquivo)]
(biff, embedded labels);
Next Arquivo
Next Pasta

Meses:
Left Join(Tabela)
LOAD * Inline [
Mês, MêsNum
JANEIRO,01
FEVEREIRO,02
MARÇO,03
ABRIL,04
MAIO,05
JUNHO,06
JULHO,07
AGOSTO,08
SETEMBRO,09
OUTUBRO,10
NOVEMBRO,11
DEZEMBRO,12
];

Store * from [Tabela] into [lib://Mkt-AUT/Implementos Rodoviários/ABIFA_Volumes.qvd] (QVD);

Exit Script;

[marcus_sommer · Accepted solution]

I assume that you don't really need what you asked else you want exclude invalide/unwanted data - probably excluding a row with calculated totals of the above details-rows. Quite usually such rows contain anywhere an information like 'Total' and empty cells for the various grouping-information and then various aggregated results.

This means such rows could be detected and removed by querying like:

where
   Field <> 'Total';
   isnum(Field);
   len(trim(Field));

or similar measures.

[heitorcarlosgomes]

Thx