Alternating Fields in Bar Chart

[#1]

I have created bar chart.

As dimension I use [Authenticated User] field for the chart.

The thing is - this field displays only Usernames, and I want it to display full names.

That is why I made LEFT JOIN with the table and I have managed to get Full names for respective usernames.

Problem is - it can rarely happen that some usernames do not have corresponding Full names (these are some special usernames)

In that case I would like my chart legend to display username and not full name.

Is that possible and if yes, please how can I do it?

Thanks

D.

edit:

is it possible to create "Dynamic dimension" with something like following code:

if

[Authenticated user] <> ''
then Full_Name
else [Authenticated user]

[Peter_Cammaert]

Delete the JOIN.

Turn your table with UserNames and FullNames into a mapping table, but without the ones that have no FullName.

When loading the original data that contains a UserName, use applymap() to map to FullName and use third parameter to use UserName when no FullName exists. E.g. like:

LOAD ..... applymap('UserName2FullName', UserName, UserName) AS FullName

FROM ...;

Now use FullName as your chart dimension. It will show a value in all cases.

If you submit an example document, or a script, we can show you how to do this in your own code.

Good luck,

Peter

[tresesco]

In the script after join, you might have got two fields like Name and FullName, create another field something like:

Load

          Name,

          FullName,

          If( IsNull(FullName) or Len(Trim(FullName))=0, Name, FullName)  as EffectiveName

Resident <>;

Now use this 'EffectiveName' field in the chart.

[rustyfishbones]

Create a Mapping Table

here is a video of how to create a mapping table using ApplyMap

Qlikview ApplyMap Function by RFB 90 - YouTube

[#5]

use calculated dimension in the bar chart :

if(isnull(full name),username,full name)

Regards

[#6]

Since I have to make this changes on more than one place/charts (username -> full name), applying mapping table seems as a fantastic suggestion.
Please find the code here: https://gist.github.com/anonymous/6978291

Furthermore, as mentioned above, there are some usernames which do not have corresponding full names (special users). in this case chart legend needs to show corresponding username.

Thanks again!

D.

[Peter_Cammaert]

Here you go:

//--- Change original code:

Sessions:
Load  //...
      upper([Authenticated user])  AS [Authenticated user]
      //...
FROM $(LogPath)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels); 

LEFT JOIN (Sessions)
LOAD 'AUSTRIA\' & upper(NT_USER) AS [Authenticated user],
// Vorname & '|' & Nachname as Full_Name
    trim(upper(Vorname)) & ' | ' & trim(upper(Nachname)) as Full_Name
FROM QVD\VWL_User.qvd(qvd);

//--- Into this:

// Put this one above the first applymap() call
// Note: column names are not important in mapping tables
MapUsername2FullName:
MAPPING LOAD 'AUSTRIA\' & upper(NT_USER) AS IndexField,
        trim(upper(Vorname)) & ' | ' & trim(upper(Nachname)) as ResultField
FROM QVD\VWL_User.qvd(qvd)

WHERE len(trim(Vorname)) > 0;

// Note: third applymap parameter will be used if Mapping Table returns no value
Sessions:
LOAD //...
    upper([Authenticated user]) AS [Authenticated user],
    applymap('MapUsername2FullName', upper([Authenticated user]) , upper([Authenticated user])) as Full_Name
    //...
FROM $(LogPath)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);

Good Luck,

Peter

[#8]

What am I doing wrong here:

LEFT

JOIN (Sessions)
LOAD
'AUSTRIA\' & upper(NT_USER) AS [Authenticated user],
// Vorname & '|' & Nachname as Full_Name
trim(upper(Vorname)) & ' | ' & trim(upper(Nachname)) as [Full Name],
// if(Len(Vorname) <= 3, upper(NT_USER), trim(upper(Vorname)) & ' | ' & trim(upper(Nachname)) ) as [Effective Name]
// if(Vorname = null(), upper(NT_USER), trim(upper(Vorname)) & ' | ' & trim(upper(Nachname)) ) as [Effective Name]
if(IsNull(Vorname), upper(NT_USER), trim(upper(Vorname)) & ' | ' & trim(upper(Nachname)) ) as [Effective Name]
FROM
QVD\VWL_User.qvd
(qvd);

It should show upper(NT_USER) when Vorname NULL is, but that does not happen.

[Peter_Cammaert]

And if you use:

:

if(len(trim(Vorname)) > 0, trim(upper(Vorname)) & ' | ' & trim(upper(Nachname)), upper(NT_USER) ) as [Effective Name]

:

Peter

[#10]

If I try that, I get the same result.
Do you maybe have any other suggestion?