topic Re: How to get a particualr string? in QlikView

How to get a particualr string?

deepti_singh — Fri, 17 Apr 2015 17:46:02 GMT

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

Re: How to get a particualr string?

sunny_talwar — Fri, 17 Apr 2015 18:03:59 GMT

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

Re: How to get a particualr string?

MK_QSL — Fri, 17 Apr 2015 18:10:44 GMT

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])

Re: How to get a particualr string?

deepti_singh — Fri, 17 Apr 2015 18:26:02 GMT

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.

Re: How to get a particualr string?

sunny_talwar — Fri, 17 Apr 2015 18:29:57 GMT

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

Re: How to get a particualr string?

MK_QSL — Fri, 17 Apr 2015 18:38:11 GMT

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.

Re: How to get a particualr string?

deepti_singh — Fri, 17 Apr 2015 18:39:15 GMT

Got it. Thanks!

Re: How to get a particualr string?

deepti_singh — Fri, 17 Apr 2015 18:42:27 GMT

Got it!. Thanks.