NoOfRows() issue for single value table

[Yoann]

ListFileBudget is storing the name of some particular files. In my case, only one file is matching, resulting in a  single value table. While trying to access to every file contained in ListFileBudget, the NoOfRows() function is returning the number of rows of the only table stored in ListFileBudget (24000 rows), instead of the actual number of rows of ListFileBudget (1 here). This way, the for...next block is loading 24000 times the same data.

ListFileBudgetTB:
LOAD FileName() as BudgetTB_File_Name
From D:\Qlikview\Budget_D_TB_*.xlsx
(ooxml, embedded labels, table is Feuil1);

FOR i = 0 to NoOfRows('ListFileBudgetTB')-1;

LET vFileNameBudgetTB = Peek('BudgetTB_File_Name', $(i), 'ListFileBudgetTB');

Budget_initial:
LOAD 
[...]
From [D:\Qlikview\$(vFileNameBudgetTB)]
(ooxml, embedded labels, table is Feuil1);

NEXT

DROP Table ListFileBudgetTB;

Would you have any solution or alternative for this issue ?

Thank you,

Yoann

[Gysbert_Wassenaar]

There are lots of solutions
1. Load only the first record from that table: FIRST 1 LOAD FileName() ...etc
2. Load only distinct values: LOAD DISTINCT FileName() ...etc
3. Get rid of the For loop and load only the filename from the first record: LET vFileNameBudgetTB = peek('...', 0, '...')
4. Get rid of the For loop and load only the filename from the last record: LET vFileNameBudgetTB = peek('...', -1, '...')
5. Iterate only once in the for loop: FOR i = 0 to 0 ...

[Yoann]

Thank you for your answer.

Though I have just one matching file in this case, the script must be able to handle several files while considering the single file case.

Also, would you have an explanation for the NoOfRows() returning the number of rows of the only element ?

[Gysbert_Wassenaar]

If you load only one excel file then there can only be one filename value returned by FileName(). So, I don't understand your requirement of handling several files. You only process one file.

NoOfRows returns the number of rows in the table. It does not return a distinct count of values in a field. Your table has 24000 rows because the table from the excel file it loads has 24000 rows. The FileName function simply fills in the same filename for each of the 24000 rows.