how to highlight a row based on existence of another row

[pkrouglov]

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?

[sunny_talwar]

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?

[pkrouglov]

Hi,

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

[sunny_talwar · Accepted solution]

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

[pkrouglov]

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. 

     

[sunny_talwar]

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

Script:

Table:

LOAD * Inline [

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';

[pkrouglov]

Thanks Sunny,

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

[sunny_talwar]

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?

[pkrouglov]

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

[sunny_talwar]

Agreed