Value Handling Variable - OtherSymbol

[sunny_talwar]

While browsing for something on QlikView help, I came across Value Handling Variable called OtherSymbol (ValueHandlingVariables/OtherSymbol). I tried to read the help, but not sure I understand what it is doing. Can somebody shed some light on this

SET OtherSymbol='+';

LOAD * INLINE [
X, Y
a, a
b, b
c, c
d, d
e, e
];

LOAD * INLINE [
X, Z
a, a
d, d
+, c
e, e
];

 Output is this

Specifically curious on why X = e, Y = e getting repeated. This has something to the order in which the X = + row is loaded... because if I load this

SET OtherSymbol='+';

LOAD * INLINE [
    X, Y
    a, a
    b, b
    c, c
    d, d
    e, e
];

LOAD * INLINE [
    X, Z
    a, a
    d, d
    e, e
    +, c
];

I get just one row for X = e, Y = e

[uacg0009]

Hi Sunny,

Firstly I want to say that I learnt a lot from your reply before in Qlikivew Community, and I really hope that one day I can be strong enough like you in Qlikview.

This is my first time to see the "OtherSymbol", I think it's a cool function when doing join.

And I tried in my desktop. I think:

The "+" means all values except all above values.

So when you put it on the third column, the "+" means (b,c,e).

when you put it on the last column, the "+" means (b,c).

I tried to put it on the first column, then I got 8 rows in the table. You can try it.

Thanks.

Aiolos

[marcus_sommer]

I never used this feature before and I'm also not really sure how it worked and what might be a real use-case for it (and if it had then really significantly advantages against other ways to solve the required task - especially in regard to control that only certain values/field-values are transformed).

I didn't yet a testing but I think it would be useful / necessary to include a recno() into the loadings and to view the results within the tableviewer because a tablebox within the UI creates already a join and it might lead to misinterpreting. Further it might be interesting to look with fieldvalue() on the results (maybe with an additionally load/loop) to see what is really stored.

- Marcus

[sunny_talwar]

---

@marcus_sommer wrote:

what might be a real use-case for it

---

This is where it really got me. What can be a use case for such a thing. Where might I feel that something like this will come in handy. May be we can invite other people to seek there response on it's usage

@rwunderlich, @hic

[sunny_talwar]

thanks @uacg0009!!.

I sort of get what it is doing, but I am looking to understand where will such a behavior will come in handy.

[uacg0009]

Hi Sunny,

Actually I met a request several weeks ago, I think if I know the "othersymbol", maybe I will try to use it.
Fox example, we have different countries data, like US,UK,China,Japan and so on.
And we have a new table now, we want to left join the new table to the fact table.
For new table, we may have:
Country, Value
US, 100
UK, 200
China, 300

And for other countries, I want to give a default value like 50.
I think we can use the "othersymbol".

Aiolos

[juraj_misina]

Kind of late to the party, but did anybody move forward with this? I THINK this might be usefull for missing dimension values where I could concatenate something like

LOAD * inline [
ID, Label
+, OTHER
];

To any dimensional table, not caring about what are actual IDs which are in the fact table but not in dim table. Will give it a try.

[Kushal_Chawda]

@sunny_talwar  just found the below support article. Got the first use case seems 😊

https://support.qlik.com/articles/000003742

[robert99]

Old post that I came across. I NOW use OtherSymbol a lot

I use it for removing Nulls (resulting from joining or associated tables). NB I also use mapping load but OtherSymbol is easier

As opposed to blank nulls when I mainly use >>> if(null (fieldname) >0, fieldname, 'asrequired') as fieldname.

I usually enter this as the last script entry

Example

To add an entry(s) in the CalTCode table. For 4 fields as shown below. This will search all table where CalT_Code exists and merge / add to / concatenate this missing field value with the CalTCode table  

Set OtherSymbol = +; 
Concatenate (CalTCode)
LOAD * Inline [
CalT_Code,Call_Catagory,CallType,CalT_Resp_Hour
+,NoEntryQlik,NoEntryQlik,NoEntryQlik
];

But note a post by HIC on this. In the comments on page 2

Finding NULL - Page 2 - Qlik Community - 1474279