topic Re: How to get Table 1 minus Table 2 Qlik Sense in App Development

How to get Table 1 minus Table 2 Qlik Sense

daniel_bernal_y — Tue, 02 Oct 2018 19:53:56 GMT

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

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

dplr-rn — Tue, 02 Oct 2018 20:02:43 GMT

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.

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

daniel_bernal_y — Tue, 02 Oct 2018 20:16:59 GMT

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

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

dplr-rn — Tue, 02 Oct 2018 20:30:48 GMT

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

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

daniel_bernal_y — Tue, 02 Oct 2018 20:53:00 GMT

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

My table must shown:

Customer

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

Thank you

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

lalphonso — Wed, 03 Oct 2018 03:24:18 GMT

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

];

Table2:

Load *,

1 as flagTable2

INLINE [

Customer

];

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.

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

valnod_90 — Wed, 03 Oct 2018 10:30:49 GMT

hi,

try with this dimension:

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

but i think you need to join the tables before.