First name and 1st letter of Last name

[arpita]

Hi all,

I want my dashboard to show only the First name and the 1st letter of the last name to show.

Eg:

Sophie Atkinson as Sophie A
Stephanie Holder as Stephanie  H
Anish Patel as Anish P

[jwjackso]

If first name and last name are separated by a space 

=SubField(Name,' ',1)&' '&Mid(SubField(Name,' ',2),1,1)

[Crichter141]

Can I add a criteria?  

Sophie Atkinson as Sophie At

Sophie Allen as Sophie Al

Stephanie Holder as Stephanie  Holde

Stephanie Hold as Stephanie  Hold

Anish Patel as Anish P

The idea being if there are two with the same first name, add a letter to the last name until there is no duplicate?

Ideas?

[marcus_sommer]

The start- and also the amount-parameter from mid() could be set from a calculation. In this case you may apply it within a properly sorted resident-load and querying the previous record with interrecord-functions like previous() or peek() - probably within several nested if-loops.

The first query may be a direct comparing of both values and then a wildmatch() to check if there is any familiarity and then comparing step by step each single char from the current und the previous record. It's not a specially nice approach but should be working and is logically simple.

Thinkable are also logic in which such comparing is done in a while-loop but it would surely need some time to get the logic working.

Beside this is there really an added value in showing the last-name with a different number of chars instead just with a single char or completely?  

[Crichter141]

Hey Marcus.

The basic situation is that there are in-person appointments.  Salesperson 1 would like a dashboard that lists all the appointments for the day.  The preferred option would be first name only (for privacy).  However, first names are not unique so how to tell Sophie 1 from Sophie 2?  

Of course the first thought was last initial.  But again not unique.  It's their idea to keep adding letters until we get to a unique name.  Still has limits what do we do with the Smiths of the world?

So we've challenged the request to see if they have another criteria we can use that is more unique...like  appointment time (hopefully unique) or phone number to tell them apart.  Now it comes down to what to display for those users that are public facing (again privacy).  

We might push the request back to our data warehouse instead of Qlik if there's no, viable solution.  But I was asked from a Qlik perspective.  If they insist on the last initial and a loop, I'd say it would work better in the DW as we're trying to not add extra reload time.  We shall see what comes of it.

Thank you!

[marcus_sommer]

I think the looping-part wouldn't be more challenging from a performance point of view as the fact that the mentioned interrecord-function needs a sorted resident-load which may entirely superfluous if not such logic would be implemented. Further several nested if-loops would be required and like mentioned the results won't be always unique for all the Smiths and Browns ... Nevertheless if there aren't millions of records the needed load-times may not be the show-stopper.

Personally I would tend to avoid this stuff and showing the first-name and the start-char of the last-name and adding any unique information like a phone-number or account-id or similar information in the way many authentication-logic supports the user, like: 12#########89 or xx######@###.com

[Crichter141]

Thank you.  I think we're going to do like the local DMV, First Name last four digits of phone number.  It's not pretty but I think it will work and be less complicated then having to loop through the names.  Also less resources.  The things we do for data...thank you agin.