Hello All, I am trying to find the users with maximum cost value.

[Anonymous]

User	Cost Points	RU	BU
Angeli	6	21	Ed
Bomil	7	43	Pu
Angeli	0	22	Ed
Angeli	6	21	Mor
Rocky	7	34	Th
Moni	0	20	Tu
Rocky	6	35	Th
Aackerman	5	34	Tu
Angeli	5	23	Mor
Aackerman	6	34	Mor
Angeli	8	21	Mor

Here, I want to find the users with maximum cost points irrespective of RU and BU. If a user has a single entry in the table with 0 cost point, so he should also appear in the list. So my output should be the following

User	Cost Points	RU	Header 4
Angeli	8	21	Mor
Bomil	7	43	Pu
Rocky	7	34	Th
Moni	0	20	Tu
Aackerman	6	34	Mor

Code I am using is:

Load Distinct

"User Name",

RU,

BU,

Std_License,

Max("Cost Points") as "Max_cost_user"

Resident Combined_data

Where "Dialog_Users"=1

Group By "User Name",RU,BU,Std_License

Order By "User Name","Cost Points" ;

But it is not producing desired result.

I would highly appreciate any help. Thank you in advance.

[sunny_talwar · Accepted solution]

Is this something you need to be done in the script? May be this

Load Distinct

"User Name",

FirstSortedValue(RU, -"Cost Points") as RU,

FirstSortedValue(BU, -"Cost Points") as BU,

Std_License,

Max("Cost Points") as "Max_cost_user"

Resident Combined_data

Where "Dialog_Users"=1

Group By "User Name",Std_License

Order By "User Name","Cost Points" ;

[Anonymous]

Yes, It worked

Thank you so much!!!

I also want to remove repeated users from the above list, and display only Std_License with Max_cost_user.

To remove repeated user I am considering First_Name, Last_Name, if first 5 character of First_Name and Last_Name matches to any record, only 1 record should display with BU, RU and maximum "Cost Point" license type. So I have added First_Name & Last_Name to above code, so it looks like:

M:

inner keep load 

    "User Name",

     FirstSortedValue(BU, -"Cost Points") as BU,

     FirstSortedValue(RU, -"Cost Points") as RU,

     FirstSortedValue(Std_License, -"Cost Points") as lic1,

     FirstSortedValue("Last name", -"Cost Points") as LName,

     FirstSortedValue("First name", -"Cost Points") as FName,

     max("Cost Points") as MaxCostPoint 

resident Combined_data 

Where "Dialog_Users"=1

Group By "User Name"

Order By "User Name";

And My code to remove repeated user is:

Qualify *;
Z:
Load
Mid(FName,1,5)&' '&Mid(LName,1,5) as Name,

FirstSortedValue(lic1, -"MaxCostPoint") as lic2,
RU,
BU
Resident M
Group By "LName", "FName"
Order By "LName", "FName";
Unqualify *;

Z1:
Load *,
if("Z.Name" = Previous("Z.Name"),1,0) as "Repeat_user"
  
Resident Z;

Drop Table Z;

Which is not working

So in short, my input table is:

User_Name	First_Name	Last_Name	BU	RU	Std_License	Cost Point
RFTSIMPSON	Tracy	Simpson	TS	210	Mu	7
TSIMPSON	Tracy	Simpson	TS	233	Pu	3
HSURRETT	Hunter	Surrett	ES	233	Lo	6
RFHSURRETT	Hunter	Surrett	ES	233	Lo	6

And the required output is:

User_Name	First_Name	Last_Name	BU	RU	Std_License	Cost Point
RFTSIMPSON	Tracy	Simpson	TS	210	Mu	7
HSURRETT	Hunter	Surrett	ES	233	Lo	6

I would highly appreciate your input, Thanks in advance

[sunny_talwar]

Try this

Table:

LOAD User_Name,

    First_Name,

    Last_Name,

    BU,

    RU,

    Std_License,

    [Cost Point],

    Left(First_Name, 5) & Left(Last_Name, 5) as Name

FROM

[https://community.qlik.com/thread/286053]

(html, codepage is 1252, embedded labels, table is @4);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Name <> Previous(Name)

Order By Name;

DROP Table Table;

[Anonymous]

Thank you so much for your reply. Will try it and let you know.

[Anonymous]

Thanks for your solution, it helped but it is not sorting users according to the cost points

Doesn’t sort by  Cost Point.

Because for my problem, I am trying to achieve single user with maximum “Cost Point” and ignoring the repeated users (Users having same First_Name and Last_Name).

I tried Order By Clause, but it’s not showing desired results either

Below is my code:

Z:

Load

ID,

FName,

MaxCostPoint,

[License Type],

LName,

Left(FName,5)&' '&Left(LName,5) as Name,

[Reporting Unit],

[Business Unit]

Resident M

Order By MaxCostPoint;

Z1:

Load *,

                                if("Name" <> Previous("Name"),'Unique', 'Repeated') as "Repeated User"

Resident Z

//Where "Name" <> Previous("Name")

Order By Name;

Drop Table Z;

[Anonymous]

Thanks for your solution, it helped but it is not sorting users according to the cost points

Doesn’t sort by  Cost Point.

Because for my problem, I am trying to achieve single user with maximum “Cost Point” and ignoring the repeated users (Users having same First_Name and Last_Name).

I tried Order By Clause, but it’s not showing desired results either

Below is my code:

Z:

Load

ID,

FName,

MaxCostPoint,

[License Type],

LName,

Left(FName,5)&' '&Left(LName,5) as Name,

[Reporting Unit],

[Business Unit]

Resident M

Order By MaxCostPoint;

Z1:

Load *,

                                if("Name" <> Previous("Name"),'Unique', 'Repeated') as "Repeated User"

Resident Z

//Where "Name" <> Previous("Name")

Order By Name;

Drop Table Z;

[sunny_talwar]

Not sure I follow... would you be able to share a sample app?

[Anonymous]

I am framing my problem again.

This is the input:

User Name	First name	Last name	Contractual User Type	MaxCostPoint
NOERNST	Norbert	Ernst	Test	0
NERNST	Norbert	Ernst	Limited Professional	7
NOERNST	Norbert	Ernst	ERP Professional	8
Mkons	Mike	konser	ERP Professional	8
Mikonse	Mike	Konser	Employee	6

Desired Output:

User Name	First name	Last name	Contractual User Type	MaxCostPoint
NOERNST	Norbert	Ernst	ERP Professional	8
Mkons	Mike	konser	ERP Professional	8

I want single user with maximum MaxCostPoint.

I tried below code, which is not working as desired.

Z:

Load

ID,

FName,

MaxCostPoint,

[License Type],

LName,

Left(FName,5)&' '&Left(LName,5) as Name,

[Reporting Unit],

[Business Unit]

Resident M

Order By MaxCostPoint;

Z1:

Load *,

                                if("Name" <> Previous("Name"),'Unique', 'Repeated') as "Repeated User"

Resident Z

//Where "Name" <> Previous("Name")

Order By Name;

Drop Table Z;

Please help

[Anonymous]

Hiii Sunny,

Do you need some more information, I have framed my problem again and posted yesterday. Please let me know if you need more clarification.

Eagerly waiting for your reply

Thank you in advance!