Showing all rows in Table Box / Straight Table

[#1]

Hi

This is a well known issue, with the usual solution being to generate a unique field value for each row, to force all rows to be shown.

It solves the problem, but i'd really prefer not to generate 200 million unique values.(consumes memory and load time)

Does anyone have a better solution?

Example:

Best regards

Frederik

[johnw]

I really thought this calculated dimension was going to do the trick:

=valueloop(1,aggr(count(Revenue),Country,Location,Date,Document,AccountCode,Customer))

But it says error in calculated dimension. If I remove the valueloop portion, it gives me the correct number that I want to loop to. I suppose it just doesn't want to do the valueloop in the context of the other dimensions. It wants the same number of values every time. Pity.

This didn't work either, which was no surprise, since I believe this version of subfield only returns multiple rows when used in the script:

=aggr(subfield(repeat('. ',count(Revenue)),' '),Country,Location,Date,Document,AccountCode,Customer)

Generalizing, what I'm searching for is some way to generate the 1-N values in a new calculated dimension, where N = count(Revenue) for that particular Country, Location, Date, Document, AccountCode and Customer. All my attempts have been failures, but perhaps it doesn't mean that the line of thought is wrong. Maybe there's a way. But I'm giving up, at least for now.

Good luck, and please let us know if you solve it. Seeing all the rows without generating a unique ID would be a good thing to know how to do, even if it rarely comes up.

[#3]

Hi John

Thanks for giving it a shot.

I'll post if I find a better solution than using unique ID's.

[johnw · Accepted solution]

I did it! It's not the cleanest thing in the world, and I'm not sure if it's better than using unique IDs, but here goes. First, add a new dimension. Something like this for your example:

valueloop(1,5)

Hide this column on the presentation tab. Then change your expressions to this:

Revenue = if(valueloop(1,5)<=count(Revenue),Revenue)
Cost = if(valueloop(1,5)<=count(Revenue),Cost)

In practice, I wouldn't want to hard code the max value for the loop, so I'd establish it from the data set. Then while I was at it, I'd build variables to simplify the use of this extra dimension across various tables:

Max:
LOAD max(RowCount) as MaxRowCount
;
LOAD count(Revenue) as RowCount
RESIDENT Data
GROUP BY Country, Location, Date, Document, AccountCode, Customer
;
LET count = 'valueloop(1,' & peek('MaxRowCount') & ')';
LET split = 'if($(count)<=count(Revenue),$1)';
;
DROP TABLE Max;

Then just use =$(count) as your dimension, and just wrap your expressions in $(split(...)), like $(split(Revenue)).

See attached.

[#5]

While all the solutions listed for this question propose adding a unique key to the table, I find it difficult to educate normal users to do this scripting on the fly when they would like to add a new table box by themselves. Is there an easy way to let users generate a key? For now, I am letting them know that they will needto select a field that will make the row unique.

[johnw]

sethu wrote:
While all the solutions listed for this question propose adding a unique key to the table, I find it difficult to educate normal users to do this scripting on the fly when they would like to add a new table box by themselves. Is there an easy way to let users generate a key? For now, I am letting them know that they will needto select a field that will make the row unique. <div></div>

I'm afraid not. If this is an issue, all of your tables of interest should have unique keys, even if it's done with something as simple as recno() during the load. Users shouldn't write script. Users should then include the unique key for the table, just like you've instructed them to do. For most of our tables of direct user interest, this unique key is fairly obvious, even to users - an order item ID, an invoice ID, a product ID, that sort of thing.

But if they stray from those to more complicated data structures, then things could get difficult to impossible for them. Some of our tables probably wouldn't even make sense to anyone without a computer science degree. Beyond a certain point, raw table viewing will stop making any sense to users. And QlikView simply isn't a raw table viewer anyway. Aggregation is fundamental to what it does, even if you can avoid the aggregation by careful field generation and selection.

[#7]

You did indeed find a solution John, very well thought out!

The GROUP BY load will take hours, so I'm experimenting with setting "count" to a high enough number to ensure duplicate rows wont occur.

It will save up a lot of memory, but with the downside of taking considerable processing to present results (our users want +100.000 rows for exports)

I'll look deeper into it later and evaluate if any rows are lost/multiplied under any circumstances. Thanks!

[Colin-Albert]

I would consider this to be a bug.

If I want to see summarised data then I would use a chart.

A common use for table objects is to export data or view the detail behind a chart.

Unless users ensure they add a unique key to their table and display it, the data exported from a QlikView table object may be inaccurate. - A bug!

[johnw]

Not inaccurate. Working as designed. A table box gives you all unique combinations of field values, period. It does this accurately. That you personally want a table box to be a raw view of a table, when that simply isn't what a table box IS, doesn't mean it's inaccurate or bugged. It just means you would prefer something different, some OTHER kind of object.

If you want a raw table viewer, my suggestion is that you suggest a NEW kind of object that does exactly that. You could make the suggestion through the customer portal. Such an object would probably have to be limited to displaying the fields from a single table, or you'll have similar confusion, with rows not necessarily being one to one with the raw table. I can't think of any reason to NOT support such an object, so perhaps they would add it at some point. It just isn't what a table box is.

[Colin-Albert]

I now see that the table box functionality is described more clearly in the Designer I training manual. Chapter 6 has as the first sentence of the Introduction paragraph ...
"Think of the table box as a combination of list boxes. "

[#11]

Hi,

It is not going to work in practice to display millions of rows in developer tool, right click and export. During export QV developer tool uses tens of gigabytes of memory.

We have servers with 256 GB memory. Whenever somebody does a crazy export (more than 100 Gb of memory per qv.exe), the server is getting slow, programs terminate unexpectedly, Windows crashes and once machine did not boot because the file system got corrupted.

There is good reason for having per-object max-memory-limit and per-object max-row-limit. Those limits prevent QVS sever from crashing when somebody tries same export from browser.

-----

Anyway, what are they going to do with 100 million rows: open in Excel and send by email , as usual ?

-Alex