Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Please help on this.
Rgds
Neville
What exactly are you trying to do? This has to do with the main script.
Thank you!
Dear Ishtdeep
Could you please write the script in a note pad & send to me. It would be a great favor done!
Regds
Neville
Script depends on your requirement. Please answer the above question.
Thank you!
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
Please help on this dear finds!
Regds
Neville
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
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
BR_SCHE:
LOAD BE_CODE, Date, CR_NUMBER, Particulars, F5, Contribution, Settlements, Balance, I
FROM
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;
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