topic Re: Show duplicate rows in TableBox in QlikView

Show duplicate rows in TableBox

pooja_sn — Thu, 03 Mar 2016 07:57:59 GMT

I am loading some data from my qvd into a tableBox which shows distinct rows only. I want all the duplicate rows to be displayed. Below are few approaches I tried.

Add RowNo() as an extra field so that it makes every row unique.
(Drawback: Not a feasible solution as the qvd contains millions of records)
Used valueLoop() and added a Straight table to display data. (Refer: https://community.qlik.com/thread/11488)
(Drawback: Straight table does not display a horizontal scrollbar. thus , user will not be able to scroll horizontally and check what all columns are available in table.)

Is there another efficient solution to achieve this?

Re: Show duplicate rows in TableBox

petter — Thu, 03 Mar 2016 20:51:50 GMT

Option 1 is really the only sensible option. Adding a single integer that is sequentially incremented is definitely the most efficient way of getting a unique ID. This allows QlikView to optimize away the symbol table for this field.

If adding one single integer column makes it not feasible I can't understand what would be good. Maybe scrolling through millions of records is not the right way of doing it then. Can you "force" the user to make some minimum selections to reduce the number of rows by an order of magnitude or two? So for example having one or more list boxes with dimensions where you set the ALWAYS ONE SELECTED VALUE restriction should do the trick.

Re: Show duplicate rows in TableBox

gsbeaton — Thu, 03 Mar 2016 21:12:28 GMT

Hi There Pooja N,

Yes, there is another way. You can use the Dual() keyword to achieve what you are trying to do, for example:

T1:
load * inline [
id, month
1, jan
2, jan
3, jan
4, jan
5, jan
6, jan
];


T2:
load
dual(month, id) as themonth
Resident T1;

Create a tablebox with themonth as the only field and it will be repeated 6 times.

Hope that helps.

Kind regards

George

www.qliktastic.com

Re: Show duplicate rows in TableBox

pooja_sn — Fri, 04 Mar 2016 08:05:42 GMT

The only drawback I see is - the size of my presentation layer qvw file increases and it takes a bit more time to reload .

Re: Show duplicate rows in TableBox

pooja_sn — Fri, 04 Mar 2016 08:13:20 GMT

Hi George,

I am not really sure about how can I incorporate the solution you suggested. The field 'id' specified in your example seems to be unique for every record of table 'T1' . I don't have any field that makes every row unique.

Re: Show duplicate rows in TableBox

jagan — Fri, 04 Mar 2016 08:21:09 GMT

Hi,

Create a new filed like below and add it in table box.

LOAD

Rowno() AS SNo,

FROM DataSource;

Hope this helps you.

Regards,

jagan.

Re: Show duplicate rows in TableBox

gsbeaton — Fri, 04 Mar 2016 10:28:44 GMT

You could just use the RowNo() function, if you are loading from a QVD, then this would work:

Load
*,
DUAL(RowNo(), Month) as Month
from your.qvd (QVD);

Every field in QlikView has 2 values, a text value and a numeric. The Dual() function lets us set them both explicitly. Adding RowNo() ensures that each field is unique.

If you want to post more of your code, I'd be happy to write something that fits what you have.

Kind regards

George

www.qliktastic.com

Re: Show duplicate rows in TableBox

avinashelite — Fri, 04 Mar 2016 10:37:32 GMT

Hi Pooja,

Approach one is the better way to handle your problem ...

try like this :

1. Add the ROWNO() function to the file where your presenting the data don't add this column in the QVD

2.Since ROWNO() produce a number , memory allocation will be must efficient

3.In the front end try to show your charts on conditional mode i.e. instead of showing 10 years of data ...have check to select the year so that your chart loading will be really fast