topic Re: cycle through all records in QlikView

cycle through all records

Thu, 19 May 2011 18:24:28 GMT

I need to cycle through each record, select two fields and perform some checks on those values, bin appropriately and then go to the next record until the end of the full set. How would I do this in a macro? I currently have it picking up all the unique identifiers, and getting a count. but after that I could figure out how to get it to select the two fields that I need that are associated with the unique identifier. If I was able to pick up the fields associated with a single unique id, how would I then return back to the full list to go onto the next id?

Thanks in advance...

Therese

Re: cycle through all records

yasus — Thu, 19 May 2011 18:51:43 GMT

Hi,

already tried the following?


FOR i = 0 to NoOfRows('FieldID')-1
   //Your operations
NEXT i

Re: cycle through all records

Thu, 19 May 2011 19:03:48 GMT

Thanks, Gregor, but it's the "Your operations" part that i need help with. If i have it select all possible IDs to be able to use something like ID.item(i).text, I run into problems with picking up the two other fields that are associate with a particular ID. Example, I have the following fields: ID, lower, and upper; lower and upper are not unique fields. for each ID I need to check the lower and upper fields. How do I get the values that are in lower and upper based on the ID that I am currently working with?

cycle through all records

chriscammers — Thu, 19 May 2011 19:06:38 GMT

How much data are we talking about here and could you please describe the operations more thoroughly. A "for" loop will get the job done but it is terribly slow and there is probably a better way to approach it with load statements.

Thanks

Chris

Re: cycle through all records

Thu, 19 May 2011 19:10:03 GMT

Approximately 400k records that will need to be checked. It can't be done on load because what I am checking the upper and lower against are vaiarbles that the user can change at any time to collect a different set of data.

Re: cycle through all records

yasus — Thu, 19 May 2011 19:40:56 GMT

Hi,

you need the peek-function in order to get access to a single value of a field with an unique ID.

Here the expample with reference to your specifications:

FOR i = 0 to NoOfRows('ID')-1
        LET  UpperValue = peek('upper', i, 'TableName');
        LET  LowerValue = peek('lower', i, 'TableName');
        IF UpperValue = LowerValue THEN //..or whatever 🙂
            //Operation
        ENDIF        
NEXT i

Hope that helps!

Re: cycle through all records

Thu, 19 May 2011 20:29:37 GMT

Hi... thanks again for you help, but I think peek is a load script function, not one that can be used in the macros. I haven't had much luck interchanging the two types of functions.

Re: cycle through all records

yasus — Thu, 19 May 2011 20:38:32 GMT

Hi Therese,

I suggest you provide us a sample application with a concrete description of the desired result, because I have completly no clue which purpose you are targeting.

Re: cycle through all records

mike_garcia — Thu, 19 May 2011 22:58:01 GMT

Hello!

Try using a combination of GetPossibleValues and Select.

An example would be:

set val=ActiveDocument.Fields("ID").GetPossibleValues
for i=0 to val.Count-1
     set Xvalue = val.Item(i).Text
     ActiveDocument.Fields("ID").Select XValue
     set val2 = ActiveDocument.Fields("OtherField").GetPossibleValues
     for a=0 to val.Count-1
          set Yvalue = val.Item(a).Text
          ActiveDocument.Fields("OtherField").Select YValue
          ' more stuff
     next
next

Mike

Re: cycle through all records

mike_garcia — Thu, 19 May 2011 23:01:54 GMT

Oh, and don't forget to Clear the Macro selections when during the routine.

ActiveDocument.Fields("ID").Clear

Mike

Re: cycle through all records

chriscammers — Fri, 20 May 2011 00:41:08 GMT

Please try to describe what you are trying to accomplish in terms of the output and the data. If you are going to loop through 400K rows I think you are going to be waiting quite a long time between each click.

Thanks

Re: cycle through all records

Tue, 24 May 2011 15:45:14 GMT

Thanks Miguel... that was close enough to get me the rest of the way.