Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

Different values presented after data being loaded!

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

1 Solution

Accepted Solutions
ElizaF
Creator II
Creator II

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

View solution in original post

9 Replies
nevilledhamsiri
Specialist
Specialist
Author

Please help on this.

Rgds

Neville

isingh30
Specialist
Specialist

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

Thank you!

nevilledhamsiri
Specialist
Specialist
Author

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
Specialist
Specialist

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

Thank you!

nevilledhamsiri
Specialist
Specialist
Author

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
Specialist
Specialist
Author

Please help on this dear finds!

Regds

Neville

ElizaF
Creator II
Creator II

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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