Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Contributor

## how to highlight a row based on existence of another row

I've got the following table

row_id,date_id,event_id,event_type, customer,value

1,1,1,t,a,10

2,2,2,t,a,15

3,2,2,t,a,20

4,2,7,t,b,17

5,3,100,v,a

6,3,200,v,a

7,5,3,t,b,30

8,6,3,t,b,50

9,7,4,t,a,100

10,8,4,t,a,12

11,9,5,t,a,18

I am showing that data in a straight table.

How can i

1) highlight rows where event_type = 't' and for the same customer could be found a row with event_type='v'.

In the sample i would highlight rows 1,2,3,9,10,11

2) highlight rows with the same event_id where event_type = 't' and next (in order of date_id) event for the same customer is an event where event_type = 'v'

In the sample i would highlight rows 2,3

Is that possible without script calculations?

1 Solution

Accepted Solutions
MVP

Here is the expression for color I used for your 1st condition

=If(Count({<customer = p({<event_type = {'t'}>})*p({<event_type = {'v'}>}), event_type = {'t'}>}row_id) > 0, LightCyan())

Can you elaborate a little on your second requirement

9 Replies
MVP

For 1) why won't you also highlight 5 and 6? those are customer a as well? Is there a reason those two are excluded?

Contributor
Author

Hi,

5 and 6 are excluded because their event_type = 'v'.

MVP

Here is the expression for color I used for your 1st condition

=If(Count({<customer = p({<event_type = {'t'}>})*p({<event_type = {'v'}>}), event_type = {'t'}>}row_id) > 0, LightCyan())

Can you elaborate a little on your second requirement

Contributor
Author

Thanks Sunny. That worked.

The following is regarding second requirement.

In my data model I have combined two data sets. In the sample they are identified by event_type.

't' - is a kind of data change log or transaction log (here I'd better rename customer to object_id)

'v' - is a data validation status. Transaction and validation processes are asynchronous, where transaction sets a "validation required" flag. There will be n..0 records of validation status for each object_id (customer) in the transaction log.

The identifier for the validation status is object_id+rule_id (event_id). Validation status contains records only for failed rules, so when validation gets succeeded, then the related record is removed from the validation status data set.

Validation status date_id indicates when last time a particular rule failure was detected.

So I'd like to highlight transactions that are followed by validation failure. This would show "bad" transactions.

MVP

For your second requirement, I was able to create a flag in the script.

Script:

Table:

row_id,date_id,event_id,event_type, customer,value

1,1,1,t,a,10

2,2,2,t,a,15

3,2,2,t,a,20

4,2,7,t,b,17

5,3,100,v,a

6,3,200,v,a

7,5,3,t,b,30

8,6,3,t,b,50

9,7,4,t,a,100

10,8,4,t,a,12

11,9,5,t,a,18

];

Left Join (Table)

LOAD date_id - 1 as date_id,

customer,

1 as Flag

Resident Table

Where event_type = 'v';

Contributor
Author

Thanks Sunny,

It confirms that I will have to rethink my data model.

MVP

Well I am sure there must be ways to do this on the front end, but I thought it would make more sense to do in the script since it makes the calculation so much easier. Do you not want to touch your data model at this time?

Contributor
Author

It is OK to change the model. In this case it would not be even a big change.

MVP

Agreed

Community Browser