topic Alternating Fields in Bar Chart in QlikView

Alternating Fields in Bar Chart

Mon, 14 Oct 2013 14:48:48 GMT

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

edit:

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

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

Re: Alternating Fields in Bar Chart

Peter_Cammaert — Mon, 14 Oct 2013 14:54:05 GMT

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

Re: Alternating Fields in Bar Chart

tresB — Mon, 14 Oct 2013 14:56:55 GMT

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.

Re: Alternating Fields in Bar Chart

rustyfishbones — Mon, 14 Oct 2013 14:58:09 GMT

Create a Mapping Table

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

Qlikview ApplyMap Function by RFB 90 - YouTube

Re: Alternating Fields in Bar Chart

Mon, 14 Oct 2013 15:14:10 GMT

use calculated dimension in the bar chart :

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

Regards

Re: Alternating Fields in Bar Chart

Mon, 14 Oct 2013 16:28:34 GMT

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!

Re: Re: Alternating Fields in Bar Chart

Peter_Cammaert — Tue, 15 Oct 2013 10:54:39 GMT

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

Re: Alternating Fields in Bar Chart

Tue, 15 Oct 2013 12:24:59 GMT

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

Peter_Cammaert — Tue, 15 Oct 2013 12:39:28 GMT

And if you use:

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

Peter

Re: Alternating Fields in Bar Chart

Tue, 15 Oct 2013 13:01:57 GMT

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

Re: Alternating Fields in Bar Chart

Peter_Cammaert — Tue, 15 Oct 2013 13:16:22 GMT

Yes, use a MAPPING LOAD

Peter

Re: Alternating Fields in Bar Chart

Tue, 15 Oct 2013 13:33:23 GMT

hey Peter!
I considered this option.

However, I am not sure how can I in that case display, usernames that do NOT have corresponding First_NAME (Vorname) and Last_NAME (Nachname).

Is it possible to make an if statement in Sessions table that will consider this? If yes, can you please tell me how?

Re: Re: Alternating Fields in Bar Chart

Peter_Cammaert — Tue, 15 Oct 2013 13:45:46 GMT

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

Re: Alternating Fields in Bar Chart

Tue, 15 Oct 2013 14:47:05 GMT

You are completely right!
It works!

Thanks for your support