topic Re: Different values presented after data being loaded! in QlikView

Different values presented after data being loaded!

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Different values presented after data being loaded!

nevilledhamsiri — Sun, 25 Feb 2018 16:57:35 GMT

Please help on this.

Rgds

Neville

Re: Different values presented after data being loaded!

isingh30 — Mon, 26 Feb 2018 16:28:03 GMT

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

Thank you!

Re: Different values presented after data being loaded!

nevilledhamsiri — Mon, 26 Feb 2018 17:38:12 GMT

Dear Ishtdeep

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

Regds

Neville

Re: Different values presented after data being loaded!

isingh30 — Mon, 26 Feb 2018 17:39:38 GMT

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

Thank you!

Re: Different values presented after data being loaded!

nevilledhamsiri — Mon, 26 Feb 2018 17:52:09 GMT

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

Re: Different values presented after data being loaded!

nevilledhamsiri — Sat, 03 Mar 2018 14:13:10 GMT

Please help on this dear finds!

Regds

Neville

Re: Different values presented after data being loaded!

ElizaF — Sat, 03 Mar 2018 15:20:54 GMT

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

Re: Different values presented after data being loaded!

nevilledhamsiri — Sun, 04 Mar 2018 17:18:44 GMT

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;

Re: Different values presented after data being loaded!

nevilledhamsiri — Thu, 19 Apr 2018 17:39:42 GMT

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