topic TableName() function is not working with IterNo, RecNo etc in App Development

TableName() function is not working with IterNo, RecNo etc

Firefly_cam — Fri, 17 May 2024 14:33:52 GMT

So there is a logic to get TableName list:

for i=0 to nooftables()-1 TableList: Load tablename($(i)) as TableName Autogenerate 1; next i

Now I want to simplify this to a one load with Autogenerate and TableName() function returns NULL():

TableList: Load tablename(RecNo()-1) as TableName, RecNo(), RowNo() Autogenerate nooftables();

Also have tried with Preceding Load and still nothing:

TableList: Load tablename(rec_no-1) as TableName, rec_no, row_no; Load RecNo() as rec_no, RowNo() as row_no Autogenerate nooftables();

It seems like a bug to me - TableName() responds only if we put integer or recalculated variable there.
Any thoughts on this?

Re: TableName() function is not working with IterNo, RecNo etc

rwunderlich — Fri, 17 May 2024 15:28:10 GMT

I don't know if you can call it a bug. The function is coded to accept literal values for the parameter, not expressions. So I suppose changing it to accept an expression may be considered more of an "enhancement".

-Rob

Re: TableName() function is not working with IterNo, RecNo etc

marcus_sommer — Fri, 17 May 2024 15:36:22 GMT

I think tablename() expects an "existing" value like a variable would be providing because each variable is evaluated before the statement/object. By applying a function or even field-values it requires a processing.

I don't know if there are any special requirements to access this kind of meta-data or if it's rather a bug within the underlying function - maybe in regard to the scope/validity of variables/parameter. From my point of view is the "pain" with this behaviour not heavy because the for loop is also quite readable.

Re: TableName() function is not working with IterNo, RecNo etc

Firefly_cam — Fri, 17 May 2024 20:17:47 GMT

Hi Rob, thanks for reply
I called it as a bug because similar subject FieldName() function works fine both with Preceding Load and IterNo() etc

TableFieldList: Load Table, FieldName(IterNo(),Table) as FName While IterNo() <= NoOfFields; Load tablename(0) as Table, NoOfFields(tablename(0)) as NoOfFields Autogenerate 1;

Re: TableName() function is not working with IterNo, RecNo etc

Firefly_cam — Fri, 17 May 2024 20:18:27 GMT

Hi Marcus,
For Loop works just fine but it generates a lot of useless rows in script_log file. Besides it is significantly slower (minimum twice) than Autogenerate.
Here's a simple example of generating 2 tables 10mil rows each
For Loop:

For i=1 to 10000 Table1: Load Chr( Floor(rand() * 26) + 65) as %Key, RecNo() as RecNo1 Autogenerate 1000; Table2: Load Chr( Floor(rand() * 26) + 65) as %Key, RecNo() as RecNo2 Autogenerate 1000; Next i

One load with autogenerate 10 mils

Table1: Load Chr( Floor(rand() * 26) + 65) as %Key, RecNo() as RecNo1 Autogenerate 10000000; Table2: Load Chr( Floor(rand() * 26) + 65) as %Key, RecNo() as RecNo2 Autogenerate 10000000;

Re: TableName() function is not working with IterNo, RecNo etc

rwunderlich — Fri, 17 May 2024 21:05:39 GMT

I see your point. I can't actually find TableName() or NoOfTables() in the current documentation, so I don't know what Qlik's intention is. The last doc reference I could find is from QV 11 that shows number is to be a literal:

TableName( 'TableNumber' )

You could try reporting it (and the missing doc) as a bug and see where it gets to.

-Rob

Re: TableName() function is not working with IterNo, RecNo etc

Vegar — Sat, 18 May 2024 06:06:51 GMT

The documentation of tablenumber() is found here: https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/TableFunctions/table-functions.htm

And here: https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/TableFunctions/table-functions.htm

Re: TableName() function is not working with IterNo, RecNo etc

rwunderlich — Mon, 20 May 2024 14:59:20 GMT

My mistake on the doc. I was thrown off because TableName() does not appear in the left-hand TOC.

Re: TableName() function is not working with IterNo, RecNo etc

marcus_sommer — Tue, 21 May 2024 07:31:00 GMT

Of course are outside load-loops significantly slower as inside load-loops especially by high amounts of iterations because of the multiple jumps of finishing a load and initializing the next one - each one with n milli/micro-seconds latency.

But by querying the existing tables within a data-model there are usually 5 - 20 tables which won't cause any performance issue if they are queried with a for-loop. You are right that all outside load-loops are resulting in badly readable log-entries but the log-file itself is rather ugly and probably seldom used regularly (personally nearly never during the development - my common mistakes of typos or not set/removed commas, quotes and so on are quite obvious - and within the production I jump only to the end to go to the error).

Nevertheless if you want to analyse it more you may use the script-log analyser from Rob and Clever:

Search Recipes | Qlikview Cookbook

Beside this I could imagine that tablename() isn't a "real" function else only a reference respectively a pointer to the RAM address and declaring it as function is just a way to make it accessible.

Re: TableName() function is not working with IterNo, RecNo etc

Firefly_cam — Tue, 21 May 2024 08:26:02 GMT

Thanks for your comments, all are relevant indeed

As for the topic itself, I kept playing with it and appears the evaluations can be done within both TableName() and TableNumber() functions and they work as expected. Counter functions and any operations with the field values are not working and this seems to be an undocumented limitation for both functions. Closing this topic with accepting this limitation as a fact.

Re: TableName() function is not working with IterNo, RecNo etc

marcus_sommer — Tue, 21 May 2024 11:58:48 GMT

Just a hint to the documentation which doesn't contained an information anymore which has probably not (entirely) changed n the years and this is that (all) table/field-functions expects (normally) a string as table/field-name and not a normal table/field-call - means also a call of:

tablenumber('My Table')

If I remember correctly this was a must in the old days. Nowadays it might be often working without it because the context of a call defined the interpretation ... but honestly I'm not sure that this is always true.

Beside this I played a bit more with the matter and found some kind of workaround by using:

t1: load recno()-1 as R autogenerate nooftables();
t2: load evaluate('tablename(' & R & ')') as TableName, R resident t1;

t3:
load evaluate('tablename(' & RecNo & ')') as TableName;
load recno() -1 as RecNo autogenerate nooftables();

which means it worked by a resident load as well as by a preceding but not including recno() - within the evaluate - only recno() will work!

Someone with ideas why it behaved as it behaved?

Re: TableName() function is not working with IterNo, RecNo etc

Firefly_cam — Tue, 21 May 2024 12:33:28 GMT

Thanks for the workaround and in fact this even works like this:

Tab2: load evaluate('tablename(' & RowNo()-1 & ')') as TableName autogenerate nooftables(); or Tab2: load evaluate('tablename(' & RecNo()-1 & ')') as TableName autogenerate nooftables();

Idk why and the IterNo() still doesn't work even like this.