Identifying and highlighting multiple values in a single column with reference to another Column.

[sanjaya9203]

Hi All,

I have  a data table containing following columns.

Item No.	Country	Article Number
R6A57A	USA	4549821364213
R6A57A	UK	190017452944
P28517-B21	USA	4549821364008
P28517-B21	UK	190017453422
P28512-B21	USA	4549821363995
P28512-B21	UK	190017453415
R6A55A	USA	4549821363988
R6A55A	UK	190017453408
R6A54A	USA	4549821363971
R6A54A	UK	190017450896
R6A53A	USA	4549821363964
R6A53A	UK	190017450889
R6A52A	USA	4549821363957
R6A52A	USA	4549821364206
R6A52A	UK	190017450872
P28417-B21	UK	190017452913
Q9V07B	USA	4549821364732
Q9V07B	UK	190017452661
Q9V04D	USA	4549821364602
Q9V04D	UK	190017452630
Q9V04D	UK	190017455389
Q9V05E	USA	4549821364619

I have to check and Display the below table in qlik sense based on the following condition.

1. Each Item No having Only one country name and one Article No. (As highlighted in Blue Color). and no need to do anything

2. If an Item No. have same  country name having more than one  Article No. then, i need display in report in tabular format for those have multiple article no with same country name in different color say in Red color. (as per below table)

My expected output format is as follows.

Expected Result
Item No.	Country	Article Number
R6A52A	USA	4549821363957
R6A52A	USA	4549821364206
Q9V04D	UK	190017452630
Q9V04D	UK	190017455389

E.g here the Item No.= R6A52A and country= USA, but it has two Article no. hence it is an error and the same need to be display in table. similarly for Item No Q9V04D.

I need the solution either in script or in front end part or both. I tried to do in back end but I could not achieved  so far.

your help is highly appreciable. 

Thanks in Advance.

[msKarthikeyan · Accepted solution]

Hi

You can enter this in the text color expression in the table

if(aggr(nodistinct count([Article Number]), [Item No.], Country) > 1,rgb(255,128,0))

Change the rgb values to select the color you want. 

-Karthik 

[sanjaya9203 · Accepted solution]

Thank you Karthik for your quick response.

I followed you and used the expression but it colors all values not for those particular values. Please check below picture.

Also i would like to tell you that I need only those Four Records instead of all records  as i mentioned Red color Table in

my original post. I am not using any filters to filter the Item No. or Article No.

.

Thank you once again. Please help me in this regard.

[msKarthikeyan · Accepted solution]

Hi,

It was working for the 21 records you shared..

For displaying only those 4 records , i changed the dimension article No. in the chart to 

=if(aggr(nodistinct count([Article No.]), [Item No.], Country) > 1,[Article No.],Null())

and uncheck 'Include Null Values'

- Karthik 

[sanjaya9203]

Hi karthik.

Now it's working fine. I did a small mistake in script, that's why i didn't got desired result.

Thank you So much Karthik.