topic Re: EXISTS in QlikView

EXISTS

upaliwije — Mon, 24 Aug 2015 16:25:38 GMT

I have two data table

Table 1

CLNO, VALUE

1 50

2 40

3 60

4 100

Table 2

CLNO, VALUE

1 50

2 40

3 60

4 100

5 20

6 30

7 90

8 80

9 10

10 20

I want to load data in the second table minus records in the first table. How can I do it pls help

Re: EXISTS

Mon, 24 Aug 2015 16:29:35 GMT

Hi Upali,

when loading table two, you can use

where not exists(CLNO);

hope that helps

Joe

Re: EXISTS

julian_rodriguez — Mon, 24 Aug 2015 16:32:55 GMT

Hello UPALI,

Assuming that CLNO is a key field or the row can be identified by this field, you can use "Where not Exists"

Table1:

LOAD * INLINE [

CLNO, VALUE

1, 50

2, 40

3, 60

4, 100

];

NoConcatenate

Table2:

LOAD * INLINE [

CLNO, VALUE

1, 50

2, 40

3, 60

4, 100

5, 20

6, 30

7, 90

8, 80

9, 10

10, 20

]

Where Not Exists(CLNO);

Drop Table Table1;

See attachement

Re: EXISTS

upaliwije — Mon, 24 Aug 2015 16:33:05 GMT

Pls write the script

Re: EXISTS

upaliwije — Mon, 24 Aug 2015 16:52:39 GMT

Thanks Your example is working. But when I apply same to my QV document it is not working My script is as follows. pls have a look at it see where I have gone wrong

select cen_int_branch_code AS BCOD,cen_cla_code AS CLA_CODE,cen_csd_ref_date,TRIM(cen_claim_no) as CLAIM_NO

,cen_acr

from ac_t_central_claim

where to_char(cen_csd_ref_date,'YYYY-MM')>='2015-06'

and to_char(cen_csd_ref_date,'YYYY-MM')<='2015-07'

and cen_acr<=50000

and cen_cla_code in('MC','M4');

NoConcatenate

LOAD

CLAIM_NO

FROM

E:\CLM\ANALYSIS\SPOT_PAID.xls

(biff, embedded labels, table is SPOT$);

ACR1:

NoConcatenate

//RIGHT KEEP

LOAD

Resident ACR

WHERE not exists (CLAIM_NO)

;

DROP TABLE ACR;

I get this message after data is loaded

Connecting to QlickView_CICL;DBQ=CICL

Connected

ACR << ac_t_central_claim 16,170 lines fetched

SPOT$ 2,434 lines fetched

ACR1 << ACR 0 lines fetched

Re: EXISTS

maxgro — Mon, 24 Aug 2015 17:37:35 GMT

try with

SPOT:

LOAD

CLAIM_NO

FROM

E:\CLM\ANALYSIS\SPOT_PAID.xls

(biff, embedded labels, table is SPOT$);

ACR1:

LOAD *

WHERE not exists (CLAIM_NO);

SQL select

cen_int_branch_code AS BCOD,

cen_cla_code AS CLA_CODE,

cen_csd_ref_date,

TRIM(cen_claim_no) as CLAIM_NO,

cen_acr

from ac_t_central_claim

where to_char(cen_csd_ref_date,'YYYY-MM')>='2015-06'

and to_char(cen_csd_ref_date,'YYYY-MM')<='2015-07'

and cen_acr<=50000

and cen_cla_code in('MC','M4');

Re: EXISTS

upaliwije — Mon, 24 Aug 2015 22:25:31 GMT

Thanks a lot