Rolling up Character Field in Qlikview

[#1]

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!!

[#2 · Accepted solution]

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.

[#3]

Thank you so much Bc. This worked!!