topic Re: Display real value not database value in QlikView

Display real value not database value

Mon, 06 Oct 2014 15:00:32 GMT

I'm pulling data from a table via an ODBC connection:

ODBC CONNECT TO Archibus (XUserId is xxx, XPasswordis xxx);
WorkRequests:
SQL SELECT "bl_id",
   "date_requested",
   "prob_type",
   "site_id",
   status,
   "wr_id"

FROM "archibus_live".afm.wrhwr;

The Status field holds a number of values which are based on an enumerated list in the source database, and appears as follows in the Qlikview list box:

What I want is to display, in the listbox, is the full descriptive value, not the shortened value from the enumerated list. For example:

AA should appear as "Assigned"

Can = Cancelled

Closed - Closed

HL = On Hold

I = Issued

S = Stopped

Whats the best way to make this change to how the data is displaying in Qlikview?

Thanks

Dan

Re: Display real value not database value

antoniotiman — Mon, 06 Oct 2014 15:09:04 GMT

Hi Dan,

in the script add an Inline table like

DescrStatus:

Load * Inline [

status,Descriptive_Value

AA,Assigned

Can,Cancelled

Closed,Closed

...

];

You can use wizard for Load Inline.

Regards,

Antonio

Re: Display real value not database value

jeremy_fourman — Mon, 06 Oct 2014 15:09:49 GMT

Might be misunderstanding what you want but the Status field does contain what is in the database; If you want the full descriptive value you could either:

1. Bring it back via the SQL in a join if the lookup table exists

2. The field from the table that contains the value

3. Use an applymap function in your load script and map them, this would require bringing back the table that contains the descriptiive value by itself.

4. If the full descriptive value does not exist in the source database you could create an inline table that contains the description you want and applymap using that table.

Hope that helps.

edit: I see an example of an inline load was given above.

Re: Display real value not database value

maxgro — Mon, 06 Oct 2014 17:47:46 GMT

MapStatus:

Mapping Load * Inline [

from,to

AA,Assigned

Can,Cancelled

Closed,Closed

...

];

ODBC CONNECT TO Archibus (XUserId is xxx, XPasswordis xxx);
WorkRequests:

Load

applymap('MapStatus', status) as status_description;
SQL SELECT "bl_id",
"date_requested",
"prob_type",
"site_id",
status,
"wr_id"

FROM "archibus_live".afm.wrhwr;

Re: Display real value not database value

Tue, 07 Oct 2014 11:59:43 GMT

Hi Massimo

I've put together the following based on your suggestion but get errors, as shown in the following screen prints (script as shown below): Can you advise on whats wrong with what I've tried

Thanks

Dan

StatusMap:
Mapping LOAD
       status,
       status_desc
Inline[
status,status_desc
R,Requested
Rev,Reviewed but On Hold
Rej,Rejected
A,Approved
AA,Assigned to Work Order
I,Issued and In Process
HP,Awaiting for SLFD-Finance Approval
HA,Stakeholder Meetings
HL,On Hold Labour Parts Access
S,Stopped
Can,Cancelled
Com,Completed
Clo,Closed
];

WorkRequests:
load
       *,
       ApplyMap('StatusMap',status, 'Unknown') as status_desc;
SQL SELECT "bl_id",
   "date_requested",
   "prob_type",
   "site_id",
   status,
   "wr_id"
FROM "archibus_live".afm.wrhwr;

Re: Display real value not database value

maxgro — Tue, 07 Oct 2014 12:26:21 GMT

add a space in

Inline[

it should be

Inline [

Re: Display real value not database value

Tue, 07 Oct 2014 12:35:02 GMT

Brilliant, thanks Massimo. It works fine now and I've lots of other similar fields from the source database with the same prinicple that I can apply this solution too.

spent all morning trying variations of the script to get it working, its always something minor like a commor, full stop of missed "space" that catches you out.....

I'll mark your first reply as correct!