Different values presented after data being loaded!

[nevilledhamsiri]

HI,

Appreciate if reference is made to the Excel data & the data after being loaded. the total shown in excel vs P/T looks different. Can you please look in to this & inform where I have gone wrong. Is it due to the way the script was made or due to data linking error.

Regds

Neville

[nevilledhamsiri]

Please help on this.

Rgds

Neville

[isingh30]

What exactly are you trying to do? This has to do with the main script.

Thank you!

[nevilledhamsiri]

Dear Ishtdeep

Could you please write the script in a note pad & send to me. It would be a great favor done!

Regds

Neville

[isingh30]

Script depends on your requirement. Please answer the above question.

Thank you!

[nevilledhamsiri]

Yes, There are two data sets & I need to reconcile the records between two data sets & if records in one data set equal to the same record in other data set ( In rupee value) that should be ok & if one record in one data set does not match with the other data set such as (it is completely missing or  amounts are different etc) such record to be found as not in order.   Once this unmatched records are noted  I will further see why the differences are. If I can match this two tables & get the un matched records that will be ok. Is it due to the if statement in the script or otherwise? Below is the expression used to find out matched & unmatched records

*,IF((BALAMT-Balance)<>0,'NO','OK')AS OK_NOT

Please see whether my explanation is adequate for you to proceed!

Thanks

Neville

[nevilledhamsiri]

Please help on this dear finds!

Regds

Neville

[ElizaF · Accepted solution]

Hi Neville,

See  below script.

BR_SCHE:

LOAD BE_CODE, Date, CR_NUMBER,

Particulars, F5, Contribution, Settlements, Balance, I

FROM [REF(SCHE).xlsx] (ooxml, embedded labels, table is Sheet1);

REFUND_NEW:

LOAD BR_CODE, TRN_DATE, CRE_NUMBER, DES, POL_NUMBER, F6, BALAMT

FROM [REF_HO.xls] (biff, embedded labels, table is Sheet1$);

outer JOIN (REFUND_NEW)

LOAD

CR_NUMBER AS CRE_NUMBER,

sum(Balance) as Balance,

count(CR_NUMBER)as [CR_NUMBER no]

Resident BR_SCHE

group by CR_NUMBER;

FINALTABLE:

LOAD

*,IF((BALAMT-Balance)<>0,'NO','OK')AS OK_NOT

RESIDENT REFUND_NEW;

DROP TABLES REFUND_NEW,BR_SCHE;

What I made:

1. Change the order of loading the data sources

2. Use Outer Join instead Left Join - in RA0017C0009696 doesn't exist in REFUND_NEW

3. Grouped the transaction by CR_NUMBER

Regards

[nevilledhamsiri]

Hi Filip,

Thanks so much for your information & now my 1st issue was resolved. But when I view the Not agreed records, it seems some agreed records too are appearing. (Agreed records refer to same cre_numbers with same value & below PT shows the agreed records when not ok dimension is viewed. Is it due to an error in the if statement written or otherwise.

Thanks

Neville

script is as beow!

REFUND_NEW:

LOAD BR_CODE, TRN_DATE, CRE_NUMBER, DES, POL_NUMBER, F6, BALAMT

FROM (biff, embedded labels, table is Sheet1$);

BR_SCHE:

LOAD BE_CODE, Date, CR_NUMBER, Particulars, F5, Contribution, Settlements, Balance, I

FROM (ooxml, embedded labels, table is Sheet1);

Outer JOIN (REFUND_NEW)

LOAD

CR_NUMBER AS CRE_NUMBER,

sum(Balance) as Balance,

count(CR_NUMBER)as [CR_NUMBER no]

Resident BR_SCHE

group by CR_NUMBER;

//Balance

//Resident BR_SCHE;

FINALTABLE:

LOAD

//*,IF((BALAMT-Balance)<-50 OR   ( BALAMT-Balance)>50 ,  'NO','OK')AS OK_NOT

//*,IF((BALAMT-Balance)<>0,'NO','OK')AS OK_NOT

*,IF((BALAMT-Balance=0),'ok','no' ) AS OK_NO

RESIDENT REFUND_NEW;

DROP TABLES REFUND_NEW,BR_SCHE;

[nevilledhamsiri]

HI Filip,

This was ok. But please explain me on the followings!

sum(Balance) as Balance,

count(CR_NUMBER)as [CR_NUMBER no]

What make by renaming above two expression (Balance) & dimension (CR_NUMBER)?

Also please explain why word sum before Balance?

Many Thanks

Neville