EXISTS

[upaliwije]

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

[#2]

Hi Upali,

when loading table two, you can use

where not exists(CLNO);

hope that helps

Joe

[julian_rodriguez]

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

[upaliwije]

Pls write the script

[upaliwije]

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

[maxgro · Accepted solution]

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');

[upaliwije]

Thanks a lot