How to get a particualr string?

[deepti_singh]

Hi,

I have the data in the following format:

Person           Ticket_Number         Attrition Risk             

   John                  XYZ                        Green                  

   John                  ABC                        Red

   John                  PQR                        Yellow    

   Bob                    DEF                        Green

   Bob                    LMN                        Yellow

   Robert               HIJ                          Green

green being the lowest level, then yellow followed by red which is the highest.

One person has multiple tickets and each ticket has an attrition risk category. I need to display a new column called Status in a pivot table which always shows the highest level of attrition risk for a given number of tickets for a person. For example the result should be like this:

Person          Status

John               Red

Bob                Yellow

Robert            Green

Thanks.

Regards,

Deepti

[sunny_talwar]

PFA

Table:

LOAD Person,

  [Ticket Number],

  If([Attrition Risk] = 'Red', Dual('Red', 3), If([Attrition Risk] = 'Yellow', Dual('Yellow', 2), Dual('Green', 1))) as [Attrition Risk];

LOAD * INLINE [

    Person, Ticket Number, Attrition Risk

    John, XYZ, Green

    John, ABC, Red

    John, PQR, Yellow

    Bob, DEF, Green

    Bob, LMN, Yellow

    Robert, HIQ, Green

];

Join(Table)

LOAD Person,

  Max([Attrition Risk]) as [Attrition Risk],

  1 as Flag

Resident Table

Group By Person;

Best,

Sunny

[MK_QSL · Accepted solution]

Use below in your script...

Data:
Load
  Person,
  Ticket_Number,
  If([Attrition Risk] = 'Green',Dual([Attrition Risk],1),
  If([Attrition Risk] = 'Yellow',Dual([Attrition Risk],2),
  If([Attrition Risk] = 'Red',Dual([Attrition Risk],3),
  Dual([Attrition Risk],4)))) as [Attrition Risk]
Inline
[
  Person, Ticket_Number, Attrition Risk             
  John, XYZ, Green                  
  John, ABC, Red
  John, PQR, Yellow    
  Bob, DEF, Green
  Bob, LMN, Yellow
  Robert, HIJ, Green
];

Now create a Straight Table

Dimension

Person

Expression

FirstSortedValue([Attrition Risk],-[Attrition Risk])

[deepti_singh]

Hi,

I appreciate your answer but I can't use inline load here as there are thousands of persons with such multiple tickets, any other way that I can get the results?

Thanks.

[sunny_talwar]

You won't be using the inline load. You will be doing something like this (just need to add the bold part to your existing script):

yourFactTable:

LOAD *,

          If([Attrition Risk] = 'Red', Dual('Red', 3), If([Attrition Risk] = 'Yellow', Dual('Yellow', 2), Dual('Green', 1))) as [Attrition Risk];

LOAD Person,

          [Ticket Number],

          [Attrition Risk],

          OtherFields

FROM yourSource;

Join(Table)

LOAD Person,

  Max([Attrition Risk]) as [Attrition Risk],

  1 as Flag

Resident Table

Group By Person;

HTH

Best,

Sunny

[MK_QSL]

Inline Load is for example purpose only as we don't have full data set..

It would be really helpful to you if you will check what is Inline Table in QlikView Script. This would clear all your doubt, otherwise the solution provided by us would work for full data set also.

[deepti_singh]

Got it. Thanks!

[deepti_singh]

Got it!. Thanks.