topic Re: Limiting 1000 rows in straight table. in QlikView

Limiting 1000 rows in straight table.

pavansonu99 — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

i have requirement like this

i have a dashboard which contains list box with countries and a stright table that should reflect details of countries

each countries have different number of rows . if we click on each country the details of the country should be displayed on straight table.

i have countries liike this

country polulation

india 700000

japan 50000

kanya 90000

canada 40000

newyork 5000

if i click on any country i want to display first 1000 rows of each country

i have 14 dimensions and 11 expression in that 11 dimensions are hidden.

with dimension limits its not working

i have used this expression in all 11 expression definitions --- if(rowno(total)<=1000,dimension)

hear i am getting 1000 rows but for country india i has 700000 records "Object out of Memory" error is displaying

can some help how to solve this , is there any expression that i can write in calculated dimension to get 1000 rows or come out f this error.

Re: Limiting 1000 rows in straight table.

adamdavi3s — Fri, 24 Mar 2017 10:30:24 GMT

Can you just add rowno as an expression (but hide it) and then use that in your dimension limit?

Re: Limiting 1000 rows in straight table.

raajaswin — Fri, 24 Mar 2017 10:31:07 GMT

Have you tried this>

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 10:32:31 GMT

there you cant set 1000 .

even you set 1000 it will display only 100 rows

Re: Limiting 1000 rows in straight table.

raajaswin — Fri, 24 Mar 2017 10:35:05 GMT

yo its its max 100. oopss

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 10:37:27 GMT

no need to add expression what o said 1000 rows are working with mentioned expression but for country india i am getting "Object out of Memory"

i am asking any expression that i can write in calculated dimension or any idea to come out f this error.

Re: Limiting 1000 rows in straight table.

adamdavi3s — Fri, 24 Mar 2017 10:42:01 GMT

It'll be the if statement basically, qlik and if statements aren't a great mix with large numbers of rows.

Having a play and seeing what I can come up with

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 10:46:41 GMT

Hi Adam, is there any other method to solve this.

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 11:14:14 GMT

Hi Guys ,

Please help me how to solve the error "Out of Object memory"

Re: Limiting 1000 rows in straight table.

adamdavi3s — Fri, 24 Mar 2017 12:47:06 GMT

Ok this may or may not work....

New expression called check if(rowno(total)<=1000,1)

On the presentation tab you can set this to hidden.

Then for your expressions use:

if(check,expression)

I *think* this will reduce the overhead you're getting from using the if statement on every row but I don't know how Qlik calculates that well, you'd need one of the heavy hitters on here to go into more detail probably

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 13:25:24 GMT

If i do like this i will become multiple if condition.

i think then the load will be more.

Re: Limiting 1000 rows in straight table.

adamdavi3s — Fri, 24 Mar 2017 13:28:10 GMT

Then I am not understanding your scenario I am afraid.

You'll need to share an example to make the problem clearer

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 13:35:03 GMT

I have shared my scenario in question with some values.

is there any expression that i can write in calculated dimension to get 1000 records

or i can come over the error "Out Of Object Memory" while getting 1000 records from 700000 rows.

Re: Limiting 1000 rows in straight table.

adamdavi3s — Fri, 24 Mar 2017 13:41:28 GMT

and I've shared my solution to the question posed, however I think the typo in your post has confused the situation

i have used this expression in all 11 expression dimension definitions --- if(rowno(total)<=1000,dimension)

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 13:45:45 GMT

what is much confusing in this .

i said i am using 11 expressions in my table in all the expressions i am using the above if condition.

if(rowno(total)<=1000,dimension) as i said i have 14 dimensions i am using those dimension names in the if condition.

Re: Limiting 1000 rows in straight table.

adamdavi3s — Fri, 24 Mar 2017 13:47:00 GMT

Ok makes sense, I am out of ideas

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 13:50:59 GMT

Hi Adam leave the scenario

let me tell

is there any expression that i can write in calculated dimension to get 1000 records

Re: Limiting 1000 rows in straight table.

Anil_Babu_Samineni — Fri, 24 Mar 2017 14:01:36 GMT

May be this?

1) ValueLoop(1,1000)

2) ValueList('Name1','Name2','Name3','Name4',.....)

- ANIL

Re: Limiting 1000 rows in straight table.

pavansonu99 — Fri, 24 Mar 2017 14:05:03 GMT

Hi anil not values from 1-1000 i have values in dimensions that values i want to show first 1000 records.

Re: Limiting 1000 rows in straight table.

Anil_Babu_Samineni — Fri, 24 Mar 2017 14:09:38 GMT

1) We have option Dimension limits you can take First 1000

2) Or use rank in expression like

=aggr(if(Rank(Sum(Sales))<=1000,Sum(Sales)),Dimension)