topic INLINE - want unused values to be clickable, not null in QlikView

INLINE - want unused values to be clickable, not null

Thu, 04 Nov 2010 17:49:21 GMT

I need all unused values to be set to a clickable field. Currently, unused values are set to null.

There exist numeric codes. I used inline to map the corresponding definitions. However, the numeric codes list is growing, so there may be some that are not in my inline "list". I'd like to set these unmapped codes to a blank ( ' ' ) clickable value.

Here is my current code:

Note: i already take null code values and set them to a blank value so it is clickable. Just need a similar concept for unmapped codes.

left join load * INLINE [

Code, Desc

' ', ' '

2, French

3, Spanish

];

Thanks,

Stephanie

INLINE - want unused values to be clickable, not null

boorgura — Thu, 04 Nov 2010 17:53:56 GMT

Use applymap instead of left joining it.

load the inline table as:

mapping load * inline [

---

];

and then use in the Fact table load as:

applymap('Map Name', '<Expr>', 'Defualt mapping') as Output

Note: use the Expr as Default mapping - if you want it to show up as is, when unmapped.

INLINE - want unused values to be clickable, not null

Thu, 04 Nov 2010 19:55:13 GMT

Dear Rocky,

thanks so much for your quick response.

I have never used the "Fact table" type of coding that you suggested... and I am not brave on change but I love to learn :), please advise where this code resides.... I assume I simply replace "left" with "mapping" for the actual inline?

Steph

INLINE - want unused values to be clickable, not null

boorgura — Thu, 04 Nov 2010 22:21:11 GMT

Ok -

lets say you current script is like this:

LOAD a, b,c from srcfile.qvd;

left join load * inline[

---

];

Instead of doing so...

do this:

mapTable:

mapping load * inline [

---

];

Then in your main load table...

load a,b,c, applymap('mapTable', a, a) as mappedA from srcfile.qvd;

this is assuming that a is the field you want to map and default mapping would be 'a' if unmapped.

I know I went thru in vague manner. Let me know if explanation is needed.

INLINE - want unused values to be clickable, not null

Miguel_Angel_Baeyens — Fri, 05 Nov 2010 06:49:38 GMT

Hello Stephanie,

Null values are not always the same in QlikView depending on the actual datasource. In fact, when you load from an inline table, if there is a missing value it's not null, but empty.

You can set the following in your load script

If(Len(Code) = 0, 1, 0) AS NullFlag,

and later in your chart expression something like

Count({< NullFlag = {1} >} NullFlag)

To get the number of records where Code is empty.

Take a look at the MissingCount/RangeMissingCount and NullCount/RangeNullCount in the documentation just in case. These functions are used to count non-text non-numeric values (missing values) within a range of fields.

Hope that helps