topic Re: different username for same person in QlikView

different username for same person

Chiara — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone

I guess it's a very basic question, however I cannot find a solution by myself

I have 3 different tables

1st has the list of users and the number of calls they received:

Lara Croft 30

Mickey Mouse 40

2nd has the list of the same users (but different usernames..) and the number of emails they handled

Lara_Croft 20

Mickey Mouse1 40

3rd has again same users (and different usernames) and the number of order they handled

LAC Lara Croft 50

MIC Mickey Moouse 60

I want to select Mickey Mouse and have all the 3 results (calls, email, orders)

How can I do that in my script?

thank you

Re: different username for same person

Brett_Bleess — Mon, 30 Dec 2019 17:42:48 GMT

Have a look at the following specific Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Mapping-and-not-the-geographical-kind/ba-p/1463192

Here is the base URL to the Design Blog area too in case you wish to do some further searching on your own, there are hundreds of mostly how-to posts in this area by our experts, so you should find it very helpful.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Re: different username for same person

Chiara — Tue, 31 Dec 2019 09:02:29 GMT

I'll check them, thank you

Re: different username for same person

Frank_Hartmann — Tue, 31 Dec 2019 09:42:38 GMT

try like this:

Tmp: LOAD * INLINE [ User, calls Lara Croft,30 Mickey Mouse,40 ]; Concatenate LOAD * INLINE [ User, emails Lara_Croft,20 Mickey Mouse1,40 ]; Concatenate LOAD * INLINE [ User, handled_orders MIC Lara Croft,50 AIC Mickey Mouse,60 ]; Final: LOAD *, SubStringCount(User,' ') as test, replace(purgechar(if(SubStringCount(User,' ')=2,subfield(User,' ',2)&' '&subfield(User,' ',3),User),'1'),'_',' ') as ReplacedString Resident Tmp; DROP Table Tmp;

Can u share your rawdata to see all possible Username cases??

Re: different username for same person

Chiara — Tue, 31 Dec 2019 10:24:08 GMT

It was really easier than expected 🙂

I actually have one table with all different username

so I just mapped it, and applied map:

UsernamesMap:

Mapping
LOAD

[SFDC ID],
Agent
FROM
[V:\CC Internal\2019\Reporting\TABELLE\TABELLE.xlsx]
(ooxml, embedded labels, table is OPERATORE);

and then:

//-----------------------------------
//TABELLA DATI CASI RISOLTI
//-----------------------------------
LOAD Date,
ApplyMap('UsernamesMap',[SFDC ID]) as [Agent],
RISOLTI
FROM
[V:\CC Internal\2019\Reporting\INPUT DATA\CSC_INPUT_Agent Efficiency.xlsx]
(ooxml, embedded labels, table is [Casi Risolti]);

and it works perfectly

Now I will do the same for the third kind of username, mapping it as well as 'Agents'

thank you very much 🙂

Re: different username for same person

Chiara — Tue, 31 Dec 2019 10:27:47 GMT

thanks frank.

my issue was really just the different usernames, they don't have a "rule", they are just messed up. someone with a number, someone with a _ or -. however, as i have a list of those (meaning another table with row per row each person with all his different usernames), mapping did the trick

really easy, now that I know what to do 😉