Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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
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
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.
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
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.