Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
daniel_bernal_y
New Contributor

How to get Table 1 minus Table 2 Qlik Sense

Hi there,

I read two tables from Qlik Sense, and I need to get all customers that exists in Table 1 and not exists in Table 2, and show the set in a simple Table chart.

I really don't know how can I get the result.

Thank you

Daniel

Tags (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: How to get Table 1 minus Table 2 Qlik Sense

Hi Daniel,

I'd solve the problem this way.

1. Add a flag for each table source on the script

2. Use a Dimension created using the formula - see below:

Load script (Sample)

Table1:

Load *,

     1 as flagTable1

INLINE [

Customer

A

B

C

];

Table2:

Load *,

     1 as flagTable2

INLINE [

Customer

A

C

];

Expression (Dimension Expression for CustomerAOnly)

=if(flagTable1=1 and isnull(flagTable2), Customer)

Note: if you are using qualified field names - you could "unqualify" the Customer field to use the set expression.

Regards

LA.

View solution in original post

6 Replies
Partner
Partner

Re: How to get Table 1 minus Table 2 Qlik Sense

Do you want to do this at while loading the data script level? use join then

if not share some more details like example of data model etc.

daniel_bernal_y
New Contributor

Re: How to get Table 1 minus Table 2 Qlik Sense

Hi, it is not while loading data, is on a table in sheet overview. List all customers that exists in Table A and doesn't exists in Table B.

Thank you

Partner
Partner

Re: How to get Table 1 minus Table 2 Qlik Sense

No knowing your datamodel or chart difficult to help

ideally i would try to do this in data model side with some flags to indicate the logic

alternatively set analysis or if statements are the options

maybe this can help point you in right direction

Only({<TableA_RecId -= {"=TableB_RecId"}>}TableA_CustName)

Set analysis help comparing 2 fields

daniel_bernal_y
New Contributor

Re: How to get Table 1 minus Table 2 Qlik Sense

I wrote that line in my "Customer" dimension but it doesn't work.

I want to list in a Table chart all the customers that exists in Table A and doesn't exists in Table B.

Dimension "Customer" (it's a name) should have set analysis code.

Table1.Customer      Table2.Customer

----------------------       -----------------------

A                               A

B                               C

C

My table must shown:

Customer

-------------

B

Thank you

Partner
Partner

Re: How to get Table 1 minus Table 2 Qlik Sense

Hi Daniel,

I'd solve the problem this way.

1. Add a flag for each table source on the script

2. Use a Dimension created using the formula - see below:

Load script (Sample)

Table1:

Load *,

     1 as flagTable1

INLINE [

Customer

A

B

C

];

Table2:

Load *,

     1 as flagTable2

INLINE [

Customer

A

C

];

Expression (Dimension Expression for CustomerAOnly)

=if(flagTable1=1 and isnull(flagTable2), Customer)

Note: if you are using qualified field names - you could "unqualify" the Customer field to use the set expression.

Regards

LA.

View solution in original post

valnod_90
New Contributor III

Re: How to get Table 1 minus Table 2 Qlik Sense

hi,

try with this dimension:

if(Table1.Customer <> Table2.Customer, Table1.Customer)

but i think you need to join the tables before.