Solved: Showing all rows in Table Box / Straight Table - Qlik Community

Report Inappropriate Content · ‎2010-08-18

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 · ‎2010-08-20

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.

View solution in original post

Report Inappropriate Content · ‎2010-08-18

Hi Frederik ,

What is basically your concern as i am not aware of the issue u are talking about.

Report Inappropriate Content · ‎2010-08-18

Hi Santosh

The issue is, that when several rows in a Table Box/Straight Table are completely alike, only one of them will be shown. I need to show all the rows.

brenner_martina · ‎2010-08-18

Hi Frederik,

I think this is QlikView - showing at any time only distinct values even in a tablebox. So you have to create a unique key - the easiest way ist RowNo().

Report Inappropriate Content · ‎2010-08-18

Hi Martina

It would solve it yes.

But by using recno() during the QVW load i will double the loading time (it will break the QVD optimized load) and increase the QVW size by 15%.

Alternatively i can load unique values from QVD's, but since I'm loading from multiple QVD it then has to be HASH128 generated values or a similar complex key. I've tested a small complex key and it increases the document size by 30%.

This is quite an issue, as the application is already very large.

shumailh · ‎2010-08-18

That means that if there are duplicate rows, the only way to separate them is to add a uniques field, for example RowNo() as ID.
(On the other hand - what's the reason to have duplicates?)

Shumail

shumailh · ‎2010-08-18

Can you share some of the sample data with sample application?

Shumail

Report Inappropriate Content · ‎2010-08-18

Hi Shumail

I'm showing financial transactions, so it's required to have a row for each.

I've attached an example qvw with 5 rows of transactions. Only 5 is shown in tables due to 2 rows being similar.

How to display all 5 rows without adding further fields?

Report Inappropriate Content · ‎2010-08-18

You could add an expression to the straight table something like Count(Revenue) and label it "Record Count"... that wouldn't show all 5 rows but it would show the user where more than one row was loaded.

Report Inappropriate Content · ‎2010-08-19

Hi Tim

Yes that's my preferred solution too, unfortunately it is absolutely necessary to have one line for each record.