different username for same person

[Chiara]

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

[Brett_Bleess · Accepted solution]

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

[Chiara]

I'll check them, thank you

[Frank_Hartmann]

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??

[Chiara]

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 🙂

[Chiara]

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 😉