Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Alternating Fields in Bar Chart

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]

1 Solution

Accepted Solutions

Re: Re: Alternating Fields in Bar Chart

The script example with MAPPING LOAD creates a new field in Sessions called Full_Name that will contain either:

  • the concatenation of Vorname + Nachname if Vorname is Non-NULL, or
  • the value of [Authenticated user] if Vorname is empty or NULL.

This is all done by the call to applymap(). The mapping table will not have any entries for users with a missing Vorname. Therefore the lookup will fail and applymap() will resort to the value of the third parameter.

So in your chart, just use Full_Name and everything will be fine

Peter

View solution in original post

13 Replies

Re: Alternating Fields in Bar Chart

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

MVP
MVP

Re: Alternating Fields in Bar Chart

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
Honored Contributor II

Re: Alternating Fields in Bar Chart

Create a Mapping Table

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

Qlikview ApplyMap Function by RFB 90 - YouTube

Not applicable

Re: Alternating Fields in Bar Chart

use calculated dimension in the bar chart :

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

Regards

Highlighted
Not applicable

Re: Alternating Fields in Bar Chart

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.

Re: Re: Alternating Fields in Bar Chart

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

Not applicable

Re: Alternating Fields in Bar Chart

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.

Re: Alternating Fields in Bar Chart

And if you use:

:

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

:

Peter

Not applicable

Re: Alternating Fields in Bar Chart

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