Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have the following data (see attached for complete detail):
Element_Name LOB Element_DQ_Status
E1 A Completed
E1 B Underway
E2 A Not Started
E2 A Completed
I want to create a chart with Element_DQ_Status as the dimension such that my data is unique at Element_Name*LOB level.
In case, all records at the same Element_Name*LOB level have the status 'Completed', then my unique record should have the status as 'Completed'
In case, atleast one record of the same Element_Name*LOB level have the status 'Underway/Not Started', then my unique record should have the status as 'Underway'
In case, all records at Element_Name*LOB level have the status 'Not Started', then my unique record should have the status as 'Not Started'
My data should finally look something like this:
Element_Name LOB Element_DQ_Status
E1 A Completed
E1 B Underway
E2 A Underway
Can this be done? Please help!!
Hi Nipika,
try this. Assign a score for each status - 0 for not started, 0.5 for underway and 1 for completed. Then for each element*LOB combination, get the total count of repetitions and sum of scores. Then use the logic below:
if sum of scores = 0, then output = 'not started'
else if sum of scores >0 and sum of scores < repetition_count, then output = 'underway'
else if sum of scores = repetition_count, then output = 'completed'
Try the below code if you aren't able to problem solve yourself.
tableA:
load Element_Name,
LOB,
Element_DQ_Status
from tableA.qvd (qvd);
score_map:
mapping load * inline
[key , value
Not_Started, 0
Underway, 0.5
Completed, 1];
left join(tableA)
load *,
applymap('score_map',Element_DQ_Status,'Null') as score
resident tableA;
left join(tableA)
load Element_Name,
LOB,
count(Element_DQ_Status) as repeat_count,
sum(score) as final_score
resident tableA
group by Element_Name, LOB;
left join(tableA)
load Element_Name,
LOB,
if(final_score = 0,
'Not Started',
if(final_score = repeat_count,
'Completed'
'Underway')) as final_Element_DQ_status
resident tableA;
Hope this helps.
Hi Nipika,
try this. Assign a score for each status - 0 for not started, 0.5 for underway and 1 for completed. Then for each element*LOB combination, get the total count of repetitions and sum of scores. Then use the logic below:
if sum of scores = 0, then output = 'not started'
else if sum of scores >0 and sum of scores < repetition_count, then output = 'underway'
else if sum of scores = repetition_count, then output = 'completed'
Try the below code if you aren't able to problem solve yourself.
tableA:
load Element_Name,
LOB,
Element_DQ_Status
from tableA.qvd (qvd);
score_map:
mapping load * inline
[key , value
Not_Started, 0
Underway, 0.5
Completed, 1];
left join(tableA)
load *,
applymap('score_map',Element_DQ_Status,'Null') as score
resident tableA;
left join(tableA)
load Element_Name,
LOB,
count(Element_DQ_Status) as repeat_count,
sum(score) as final_score
resident tableA
group by Element_Name, LOB;
left join(tableA)
load Element_Name,
LOB,
if(final_score = 0,
'Not Started',
if(final_score = repeat_count,
'Completed'
'Underway')) as final_Element_DQ_status
resident tableA;
Hope this helps.
Thank you so much Bc. This worked!!