InfoQube IM - Community

Submitted by jimspoon on 2018/03/20 01:57

Suppose I want to create a new Y/N field, and then set this value to Y for all existing items that meet a certain filter (e.g. [URL] NOT NUL), or something more complex. Now, assume that there are thousands of items that meet this filter. What's the best way to accomplish this? Could I do this by entering a row equation for the new Y/N field, then clicking the Recalculate button?

Comments

Hi Jim,

Many ways to do this:

Show all items in a grid. Select all items. In the properties pane, check your YN field
In the URL field, add an auto-assign: M:YNField=-1. In a grid, show all items. Show the URL column. Select all values from this column. Edit > Touch
(Best) In the YN field, add a row equation: =iif([URL]<>"",-1,0) and hit the small recalculate button next to the equation
(in the current version, update will be faster if you close all grids. Fixed in the next version)

Pierre_Admin

IQ Designer

Thanks very much Pierre! I had tried method 3, but I my row equation ("[URL] is not null") didn't work. I have to study the manual page on Equations again.

I had also used your method #1, but I was only successful when I loaded only 1000 items. When I tried to load all items (28,000), the grid was taking a very long time to load, perhaps I didn't wait long enough.

I don't suppose there's a way to "load next 1000 items" (according to the designated sort order)? Might be useful in some circumstances.

My equation did not work either ?

(works here, albeit it is slow... 20 items per seconds... I'm working on improving this right now)

Pierre_Admin

IQ Designer

It worked like a charm! Every item with a URL value was assigned to the Bookmarks Y/N field. Took a long time though. Don't know how long, I came back to computer after 30 minutes and it was done. Thanks very much! Now I'll know how to do this. Lots of possibilities.

I now see how I should have done this as part of the initial import. I didn't know that during the import process, I could assign not only values from the CSV file, but also values entered manually into the field mapping dialog! I should have assigned the value "-1" to be put into the Bookmarks field for every record. As shown here -

Still very useful to learn how to populate a field with a row equation as you have described.

While trying to accomplish this population of the Y/N "Bookmarks" field, it got me wondering about how row equations work. When we use Method #1, it was clear to me what items would be modified - the items that meet the grid filter, and are displayed in the grid. Selecting all the displayed items, then checking the Y/N field, works to associate that field and value with all those items. But using Method 3 (the row equation), what items will be operated upon? It seems that IQ queried the whole database, and that the =iif row equation acted as a filter, selecting those items that contained a value in the URL field, and assigned the Bookmarks field and Yes value to those items. So it seems that the row equation acts both as a filter to specify which items will have a value added to them, and also assigning that value.

[quote=jimspoon]

I now see how I should have done this as part of the initial import.

(...)

But using Method 3 (the row equation), what items will be operated upon? It seems that IQ queried the whole database, and that the =iif row equation acted as a filter, selecting those items that contained a value in the URL field, and assigned the Bookmarks field and Yes value to those items. So it seems that the row equation acts both as a filter to specify which items will have a value added to them, and also assigning that value.

[/quote]

Hi Jim,

Great news ! Yes, assigning values at import time is the way to go, especially if you have lots of items

For method 3 (row equation), IQ finds the list of items that have a value for the current field and any of the fields in the equation (if you have 2 fields in the equation, it gets the list of items that have a value for the current field and for field1 or field2) and evaluates the equation for those items. This is faster than doing it for all items. Of course, that means that items that don't have any values for those fields don't get updated...

So, an equation such as iif([URL] is null,-1,0) would not do anything.

Pierre_Admin

IQ Designer

very good to know!