topic Re: Divide number of records in list box in QlikView

Divide number of records in list box

Tue, 25 Sep 2012 16:04:53 GMT

Hi All,

I am not sure if it is a right way to ask this question or not but I am just wondering if there is a way to divide the records of a field in various parts and create some buttons to show First 1 Million records, Second 1 Million records and Show All for example.

I have a field IP Address and I have more than 6 million records in that. If I leave to lead and process all 6 million records then it is taking too long. What I want is to divide 6 million in 6 parts one million each and then create buttons to show First 1 million, Second Million and so on. One button for to show all.

Help will be highly appreciated.

Thanks,

Re: Divide number of records in list box

whiteline — Tue, 25 Sep 2012 19:24:47 GMT

Hi.

The easiest and the fastest way is to add a field 'Group' (or million) in your script for each IP Address (into the same table).

Then you can use it in the listbox for selections.

Re: Divide number of records in list box

Wed, 26 Sep 2012 02:12:32 GMT

I am sorry whiteline, I did not get what you are saying. Can you please explain it it a bit detail?

I have attached a sample file with few sample IPs. Can you update this qvw?

Please make Group for every 100 IPs.

Thanks,

Re: Divide number of records in list box

whiteline — Wed, 26 Sep 2012 05:48:09 GMT

Just add another field with expresison like this (for every 100):

div(rowno(), 100) as Group

Re: Divide number of records in list box

Thu, 27 Sep 2012 02:15:55 GMT

I am sorry I did not get it. I have created one new field with "=Div(RowNo(),100)" and the value of this field is displayed as 0.

As I doing something wrong?

Thanks,

Re: Divide number of records in list box

jagan — Thu, 27 Sep 2012 05:38:59 GMT

Hi,

Please find attached file for solution, hope it helps you.

Regards,

Jagan.

Re: Divide number of records in list box

whiteline — Thu, 27 Sep 2012 06:42:07 GMT

LOAD [IP Address],

[1WK],

[9WKS],

[07WKS],

div(rowno(), 100) as Group

FROM

(biff, embedded labels, table is Sheet1$);

Re: Divide number of records in list box

Thu, 27 Sep 2012 15:15:58 GMT

Hi Whiteline.... (Is your name by the way:-)),

Your answer is certainly helpful. But Infact I have 3 tables which have IP address.

PV_1LW (has 1.6 million IPs)

PV_4LWS (has 2.5 million IPs) and

PV_52LWS (has 10.1 million IPs)

Can I make a general IP Groups based on IPs from all these three tables?

PS: Is there a way to find out which IPs belongs to which group?

Thanks in advacne.

Re: Divide number of records in list box

whiteline — Thu, 27 Sep 2012 18:07:37 GMT

Hi.

You should choose how assign the groups to your IPs.

I suggested the row number in the table as a simliest way, as you only said that you have too much data to process simultaneously.

There are plenty of logical ways to split it to groups. For example, classify the first number in IP address:

div(num#(subfield(IPAddressString, '.', 1)), 😎

As for three tables, if they are separate and have different meaning, you have to create groups separately for each one (but of course you can use the same logic to group them).

Re: Divide number of records in list box

Thu, 27 Sep 2012 20:18:40 GMT

Hi Whiteline,

It is very helpful and I think I am very near to my required answer.

When I used the first method you mentioned to create groups "div(rowno(), 100) as Group". I divided with 1000000 instead of 100 and it created 7 groups, starting from 0,1,....6. I have two questions here

1. The number of IPs in each group were not exactly equal to 1000000 (should it be one million records in each group)?

2. Groups started from 0. Why it started from 0 and how can I avoid that?

When I used second method "div(num#(subfield(IPAddressString, '.', 1)), 😎" it showed me 30 numbers (groups). How these are created and differnt IPs are assigned to different groups?

I used field [IP Address] at "IPAddressString".

Or simply can you please expalin second syntax?

I will be higly greatful for your help.

Thanks,

Re: Divide number of records in list box

whiteline — Thu, 27 Sep 2012 20:36:45 GMT

Hi.

I explain you both.

div(rowno(), 100) as Group

1) rowno() - gives the number of row in the loading table (the original order as it was in input file).

div() - returns the integer part of the result of the arithmetic division.

so for rows 0-99 the above expression returns '0', for 100-199 - '1', and so on.

Obviously, you can add +1 after div() to start group name from 1.

div(num#(subfield(IPAddressString, '.', 1)), 😎

2) subfield([IP Address], '.', 1) - returns the part of string with first number of IP address (0-255).

num#() - converts the string to number.

div() - look above.

Obviously, you should get about ~30 groups (=256/8) with group names depending on the first number of IP address. The group name here is just the first number of IP address divided by 8.

So first - groups IPs just by row number in an original file, second - by first number of IP.

Re: Divide number of records in list box

Mon, 15 Oct 2012 18:45:07 GMT

Hi Jagan,

I tried to apply your solution in one of my other report but its not working. I am not sure about how you set the variable in the actions for Next Page and Previous Page. I think that piece is causing the problem.

Thanks,