topic Re: Expression which evaluates rows two at a time in QlikView

Expression which evaluates rows two at a time

Mon, 16 Jan 2012 15:28:45 GMT

Hi,

I have a strange problem which I don't have a solution to.

I have a table which is organised by a unique ID. Each two rows in the table are actually two sides of the same transaction, so when organised by the ID field, consecutive rows relate to the same transaction.

There is also a username field in the table. I would like to filter the existing table so that only a certain list uf usernames appear. But I would like to be able to include both rows of the transaction if one of the usernames to be filtered appears in either row.

I assume this would involve a loop which takes two rows into consideration upon each iteration? Or is there an existing operator which may also provide functionality to step through a table two rows at a time?

Thanks for any help,

Regards,

Peter

Re: Expression which evaluates rows two at a time

swuehl — Mon, 16 Jan 2012 15:46:25 GMT

Could you post some lines of INLINE data sample?

Do you want to filter in the script or in the front end?

I think you can use a set expression with a search string to filter IDs with matching Usernames in the front end, but not very clear about it yet. Would be nice to have something to play with.

Regards,

Stefan

edit:

Created and attached a sample. The expression could just be like

=only({<ID= p(),UserName= >} UserName)

Expression which evaluates rows two at a time

hic — Mon, 16 Jan 2012 16:01:21 GMT

I would create a data model that fits your double-record table. If I understand you correctly, two records have the same “UniqueID”? If so, the following script would do what you want.

OriginalTable:
Load
UniqueID,

User as TempUser
from SourceTable ;

Users:
Load distinct
UniqueID,
TempUser as User
resident OriginalTable;

Drop Field TempUser;

An alternative definition for the record pairs could be Div(recno(),2) as PairID. This way, you would indeed see all records belonging to pairs that pertain to a specific user.

Re: Expression which evaluates rows two at a time

lucas4bi — Mon, 16 Jan 2012 17:25:33 GMT

I think you could simply load all the IDs with the choosen username, and then use InnerJoin to just filter all the Unique IDs which doesnt have those username.

MainTable:

Load * From Table;

inner join Load

UniqueId

from Table where not isnull(User);