Link two files to verify opening balances

[nevilledhamsiri]

Hi,

Following are two   Excel files which I need to link & check the agreement of balances under (BALANCE_BF) field & balance under (CLBAL) which is in the other file. Here is the sample data. I need to be able to show CLAIM_NO  & the balances under BALANCE_BF field & balances under CLBAL & if the variance is zero (BALANCE_BF-CLBAL=0) that record to be shown as in order & the rest being not in order.

Please explain me how this be achieved in a straight table.

ORACLE_CLAIM

BRANCH    POLICY_NO              NAME_OF_THE_INSURED     CLAIM_NO                   BALANCE_BF

RA00         RA0015TC0002588    MR. H.G.S. SANJEEWA          CLRA00TC16000027    2,500.00

RA00         RA00161C0003215    MR. A.L.C.U. LIYANAGE          CLRA001C17000011    400,000.00

RA00         RA00121C0002600    MR. P.K.I.U KUMARASIRI          CLRA001C17000016    0

RA00         RA00141A0002217    MR. H.A.I.S. GUNATHILAKA      CLRA001A17000060    -55,600.00

RA00         RA00161X0002415    MRS. V.A. ANULA                       CLRA001X17000038    1,000.00

RA00         RA00151X0001758    MR. K.N.P. JAYASINGHE            CLRA001X17000039    13,000.00

RA00         RA00174A0000026    MRS. N.C. GAMAGE               CLRA004A17000002         25,000.00

RA00         RA00164F0000309    MR. S. KIRUBAKARAN          CLRA004F17000014         8,000.00

RA00         RA0013TF0000070    MR. SAM.E. LIYANAGE          CLRA00TF17000018         36,000.00

RA00         RA00144X0000298    MR. P. SATHYASIWAN          CLRA004X17000016         12,000.00

FOX_CLAIM

BR_CODE    CLNO                       CLPOLNO               CLNAME                                    CLBAL

RA00         CLRA001A17000019    RA00151A000242    MR. P.M.B.M. FAHIS                      0

RA00         CLRA001C17000011    RA00161C000321    MR. A.L.C.U. LIYANAGE             35696.5

RA00         CLRA001C17000028    RA00151C000310    K.M.SIYANARIS & CO.LTD.,              0

RA00         CLRA001X17000033    RA00131X000064    MR. B.U. WARNAPALA                       0

RA00         CLRA001X17000034    RA00141X000114    MRS. K.M.P. MENIKE                       0

RA00         CLRA001C17000029    RA00171C000322    MRS. N.U. WATHTHAGE                  0

RA00         CLRA001A17000079    RA00121A000170    MISS. H.W.D.N.P. WETTASINGHE    11000

RA00         CLRA001X17000037    RA00141X000107    MRS. M.P. KUSUMAWATHI                  10000

RA00         CLRA003F17000001    RA00163F001879    G.R.C.L. DEMATAPITIYA                  250000

RA00         CLRA001D17000022    RA00171D000404    MR. K.R.L. KAHANGAMA                  80000

RA00         CLRA001X17000038    RA00161X000241    MRS. V.A. ANULA                             1000

RA00         CLRA001X17000039    RA00151X000175    MR. K.N.P. JAYASINGHE                  13000

RA00         CLRA004A17000002    RA00174A000002    MRS. N.C. GAMAGE                       25000

RA00         CLRA004F17000014    RA00164F000030    MR. S. KIRUBAKARAN                      8000

RA00         CLRA00TF17000018    RA0013TF000007    MR. SAM.E. LIYANAGE                       36000

RA00         CLRA004X17000016    RA00144X000029    MR. P. SATHYASIWAN                       12000

RA00         CLRA001X17000041    RA00151X000205    MR. K.S. NAYANANANDA                  0

RA00         CLRA001X17000042    RA00141X000081    MR. H.M.Y.R. JAYARATHNA                  10000

RA00         CLRA001X17000043    RA00161X000229    MR. B.A.L.S. ANANDA                        0

RA00         CLRA004X17000017    RA00144X000029    MR. M. CHANDRAKUMAR                   30000

Thanks in advance

Neville

[felipedl]

Hi Neville,

As i understand your problem, a simple join between the two tables will suffice since there's the CLAIM_NO field in both of them.

Lets say you have this in one table (ORACLE):

ORACLE_TBL:

Load

     ORACLE_CLAIM,

     BRANCH,

     POLICY_NO,

     NAME_OF_THE_INSURED,

     CLAIM_NO,

     BALANCE_BF

From [Excel File 1];

FOX_TBL:

Load

     BR_CODE,

     CLNO,

     CLPOLNO,

     CLNAME,

     CLBAL

From [Excel File 2];

left join(ORACLE_TBL)

Load

     CLNO as CLAIM_NO,

     CLBAL

Resident FOX_TBL;

finalTable:

Load

     *,

     if(BALANCE_BF-CLBAL=0,'In order','Different balance') as [Balance Description]

Resident ORACLE_TBL;

drop tables ORACLE_TBL,FOX_TBL;

Felipe.

[nevilledhamsiri · Accepted solution]

Dear Felip,

Thanks a lot! it worked well & achieved my objective. Please explain me the occasion such as what you propose to be used.Just be renaming a common field such as Branch or Claim no, could it be achieved?. Since I have many situations where different files to be linked, I wish to understand this file linking process in depth. The following steps you noted, I wish to learn more. Please explain  every steps you propose here. It will be a great help for me to be more familiar with the file file linking each other.

Thanks in advance!

Neville

left join(ORACLE_TBL)

Load

     CLNO as CLAIM_NO,

     CLBAL

Resident FOX_TBL;

finalTable:

Load

     *,

     if(BALANCE_BF-CLBAL=0,'In order','Different balance') as [Balance Description]

Resident ORACLE_TBL;

drop tables ORACLE_TBL,FOX_TBL;