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’)
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.
@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?
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.