Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pkrouglov
Contributor
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
sunny_talwar

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())

Capture.PNG

Can you elaborate a little on your second requirement

View solution in original post

9 Replies
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
Contributor
Contributor
Author

Hi,

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

sunny_talwar

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())

Capture.PNG

Can you elaborate a little on your second requirement

pkrouglov
Contributor
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. 

Capture1.PNG     

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

Capture.PNG

Capture.PNG

pkrouglov
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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

sunny_talwar

Agreed