topic Re: not exists records in App Development

not exists records

harsha087 — Tue, 30 Nov 2021 18:17:33 GMT

Hi All,

Below are the two tables

T1:

Dates, Cust

1/2/2021,A

2/3/2021,C

T2:

Date, Custs

1/2/2021,A

2/2/2021, A

3/2/2021, A

2/3/2021,A

1/2/2021,B

2/2/2021, B

3/2/2021, B

2/3/2021,B

1/2/2021,C

2/2/2021, C

3/2/2021, C

2/3/2021,C

OUTPUT

I need all the records which are not part of T1:

Date, Cust

2/2/2021, A

3/2/2021, A

2/3/2021,A

1/2/2021,B

2/2/2021, B

3/2/2021, B

2/3/2021,B

1/2/2021,C

2/2/2021, C

3/2/2021, C

please help

Re: not exists records

ManuelRühl — Tue, 27 Jul 2021 08:33:51 GMT

Hi,

Maybe by using a mapping table:

Tmap:

mapping load
Dates&'|'&Cust as Key,
1 as Map
resident T1;

OUTPUT:

Load * resident T2 where applymap('TMap',Dates&'|'&Cust,0)<>1;

Best Regards

Manuel

Re: not exists records

harsha087 — Tue, 27 Jul 2021 09:29:00 GMT

i m facing error

The following error occurred:

ApplyMap error: map_id not found

The error occurred here:

t2: load Dates as Date, Custs Resident t1 where applymap('TMap',Date&'|'&Cust,0)<>1

Re: not exists records

MayilVahanan — Tue, 27 Jul 2021 09:36:30 GMT

Try like below

Load Dates&'|'&Cust as Key from Table1;

Load * from Table2 where not exists(Key, Dates&'|'&Cust);

Re: not exists records

harsha087 — Tue, 27 Jul 2021 09:51:31 GMT

i m getting below error

Unexpected token: 'Key', expected one of: 'kml', 'biff', 'dif', 'fix', 'html', 'json', 'ooxml', ...

Re: not exists records

MayilVahanan — Tue, 27 Jul 2021 09:58:25 GMT

Am not user, how you are loading the data in Qliksense.

Here, Table1 & Table 2 indicates ur source(either excel, csv, db tables) .

Re: not exists records

harsha087 — Tue, 27 Jul 2021 12:00:24 GMT

Hi ,

Table1 is from SQL query

table 2 is from inline load

Re: not exists records

ManuelRühl — Wed, 28 Jul 2021 06:48:25 GMT

If T2 is an inline table, try a preceeding load.

Load the Key from T1

Then following:

Load * where not exists(Key, Dates&'|'&Cust);
LOAD * INLINE [ .......