Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

UserCost PointsRUBU
Angeli621Ed
Bomil743Pu
Angeli022Ed
Angeli621Mor
Rocky734Th
Moni020Tu
Rocky635Th
Aackerman534Tu
Angeli523Mor
Aackerman
634Mor
Angeli821Mor

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

UserCost PointsRUHeader 4
Angeli821Mor
Bomil743Pu
Rocky734Th
Moni020 Tu
Aackerman634Mor

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.

1 Solution

Accepted Solutions
sunny_talwar

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" ;

View solution in original post

11 Replies
sunny_talwar

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
Not applicable
Author

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

I am framing my problem again.

This is the input:

 

User NameFirst nameLast nameContractual User TypeMaxCostPoint
NOERNSTNorbertErnstTest0
NERNSTNorbertErnstLimited Professional7
NOERNSTNorbertErnstERP Professional8
MkonsMikekonserERP Professional8
MikonseMikeKonserEmployee

6

Desired Output:

   

User NameFirst nameLast nameContractual User TypeMaxCostPoint
NOERNSTNorbertErnstERP Professional8
MkonsMikekonserERP Professional8

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
Not applicable
Author

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!