Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

liviumac
New Contributor III

How to fill a default value if the identifier is missing in another table

hello

I do not even know how to explain what I want to achieve in a short sentence so that is why the title of this thread is ambiguous.

This is what I want to do:

Say I have 2 tables:

Table 1: Identifier, some value fields

Table 2: Identifier, Field A

the tables are linked via "Identifier"

the set of identifiers in Table 1 is not the same as the set of identifiers in Table 2: some values are common, but there are identifiers in Table 1 which are not found in Table 2 and viceversa

I need to generate a report which contains, for all the indentifiers in Table 1, the values in Field A from table 2

for those identifiers in Table 1 which are not found in Table 2, I need to put a default value which I specify

How do I do this?
or, to put it another way, how do I identify which identifiers in table 1 are not found in table 2?

In excel I would use and IF clause with "vlookup" and "IsError" and it would look like this:
if

vlookup returns an error because it cannot find one identifier from table 1 in table 2

then

put the default value which I specify

else

use the value returned by vlookup

so I guess what I'm asking is which is the equivalent of "IsError" function from Excel in Qlik Sense?


the problem is most likely trivial, but not for me

thank you

1 Solution

Accepted Solutions
kkkumar82
Valued Contributor III

Re: How to fill a default value if the identifier is missing in another table

You can use Mapping load for this, any sample data will be useful to give you an example

3 Replies
kkkumar82
Valued Contributor III

Re: How to fill a default value if the identifier is missing in another table

You can use Mapping load for this, any sample data will be useful to give you an example

OmarBenSalem
Esteemed Contributor

Re: How to fill a default value if the identifier is missing in another table

You can achieve this via mapping tables:

(the mapping table must have only 2 FIELDS)

Try this and you'll understand :

MAP:

Mapping

LOAD * INLINE [

    CODE, descr

   1, hi

    2, hola

    5, omar

];

TABLE:

LOAD

*,applymap('MAP',CODE,'Missing') as Description;

LOAD * INLINE [

    CODE

    1

    2

   3

    4

    5

    6

    7

    8

    9

    10

];

result:

Capture.PNG

Refer to this document :

http://www.learnqlickview.com/a-qlikview-tutorial-mapping-load-in-qlikview/

liviumac
New Contributor III

Re: How to fill a default value if the identifier is missing in another table

thank you, I solved my problem, it's a nice feature

Community Browser