Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

NoOfRows() issue for single value table

 

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

 

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: NoOfRows() issue for single value table

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 ...

 


talk is cheap, supply exceeds demand
Highlighted
Partner
Partner

Re: NoOfRows() issue for single value table

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 ?

Highlighted
MVP & Luminary
MVP & Luminary

Re: NoOfRows() issue for single value table

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.

talk is cheap, supply exceeds demand