Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
liviumac
Creator
Creator

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
Specialist III
Specialist III

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

View solution in original post

3 Replies
kkkumar82
Specialist III
Specialist III

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

OmarBenSalem

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
Creator
Creator
Author

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