Derive status based on multiple records for an id

Hi Experts,
I have a table below
Rid pid state overall_State
1 a exempted Non Compliant
1 b compliant Non Compliant
1 c Non Compliant Non Compliant
2 e compliant compliant
2 a compliant compliant

Need to derive this overall state by checking the condition grouping at Rid level.
ifelse(state = ‘Non Compliant’,‘Non Compliant’,
state=‘Exempted’,Exempted,
state = ‘Compliant’,‘Compliant’)

Is it based on an order?

Have you looked into first value / last value?

1 Like

every Rid can have any number of Pids and those Pids are categorized in to three i.e exempted,compliant,non-compliant.
If a Rid has atleast one non-compliant Pid then whole Rid will be tagged as ‘Non Compliant’
then follows Exempted and the last is compliant.

I would make a calculated field that sorts based on your requirements.

ifelse(state = ‘Non Compliant’,1,
state=‘Exempted’,2,
state = ‘Compliant’,3,4)

Then do your first value sorted by this field ^.

firstValue
	(
	     {state}, 
	     [ {newly_created_calculated_field} ],
	     [ {Rid}] 
	)
1 Like

@Max Thanks a lot for easy solution.
Was able to achieve the overall state as you suggested but one thing i am having difficulty the Table needs to be maitained at Rid level.
Example:
Rid overallstate
1 Non Compliant
2 Compliant
But with your solution it is mandatory to add the state column which is making multiple records for an Rid
Rid state overallstate
1 Non Compliant Non Compliant
1 Compliant Non Compliant

It is possible to apply logic based on state column but does not include state column in the table?

I don’t think you can do that with quicksight.

You would need to do that at the SQL level.

Surely I can do via SQL using listagg function.
I am actually migrating reports from Qliksense to Quicksight.
In Qliksense we have a concat function which does the same thing as listagg in Redshift.
May be Quicksight can take it as a feature update as it is used more frequently which saves lot of effort and re-writing of SQL query at different level.

Anyways thanks a ton again.

1 Like