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: 
Anonymous
Not applicable

list values where values from the other field are not in another table

Hi everyone,

I think this should be an easy one but I still cannot find a way to do it.

I have two tables linked by ID field. Table1 is about the basic information of the IDs, and Table2 is a transaction table. There is a Region field in Table1. There are various other fields in both tables. 

I wish to list out the Regions where none of the IDs from these Regions has ever shown up in Table2.

How do I do it?

Thanks in advance for any help!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Thanks Lech for trying to help. I was having difficulties understanding the "Mapping" but I finally found a way to do what I wanted:

if(aggr(count(ID), Region)=0,Region)

This dimension expression seems to be able to successfully list all the Regions that haven't got a transaction yet...

View solution in original post

3 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

first simple assumption is that ID represent region or any other dimension beeing a lower granularity of Region hierarchy data.

The simplest way of doing it is to flag records in dimensional table.

like:

FlagMap:

Mapping

Load

    ID,

    1

Resident

    Table2(Fact)

;

then in Table1 you applymap like this

Load

*,

ApplyMap('FlagMap',ID,0) as Flag

resident

Table1 (Dimensions)

then simply refer to field "Flag" and:

0 - represents excluded

1 - represents included or shown up in Fact

Solution based on expressions can also be achieved but this would be the easiest and the most efficient one i quess.

regards

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Again,

see example attached.

It is nasty code but works ok.

hth

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Anonymous
Not applicable
Author

Thanks Lech for trying to help. I was having difficulties understanding the "Mapping" but I finally found a way to do what I wanted:

if(aggr(count(ID), Region)=0,Region)

This dimension expression seems to be able to successfully list all the Regions that haven't got a transaction yet...