topic Re: DATA not correct in QlikView

DATA not correct

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

Hello Community,

I hope all is well. Could you please try to help me, my script runs well but the data in not correct comparing with the data in the database.

I used apply map because I have to map the LB and LBopex fields from the 'Table_Correspondance'

the result in one table that contains LB and the corresponding LBopex , I used a left join to also map the opex category with the LBopew in the same table

The amout in QV is superior to the amout in the database.

Could you assist me please.

My script is bellow :

maptable:

Mapping

LOAD trim (lower([LB / ERP]))as LB,

trim(lower([LB / Budget]))as LBopex

FROM

[xx]

(ooxml, embedded labels, table is Table_Correspondance);

NoConcatenate

DA_BC:

LOAD *,

Replace( LB,' ' ,'') as LB2,

Date(PO_APPROVED_DATE, 'DD/MM/YYYY')as Date_PO,

SEGMENT2 AS CDR ;

SELECT *

from *****

WHERE ****

;

Concatenate

LOAD *,

Replace( LB,' ' ,'') as LB2,

date(CANCEL_DATE, 'DD/MM/YYYY') as Date_PO,

SEGMENT2 AS CDR ;

SELECT *

from

WHERE

;

drop field LB FROM DA_BC;

NoConcatenate

SUIVI_DA_BC:

LOAD *,

trim(lower(LB2)) as LB,

AutoNumber( MakeDate( year(date([Date_PO],'DD/MM/YYYY')), Month(date([Date_PO],'DD/MM/YYYY')), day(date([Date_PO],'DD/MM/YYYY')) ) , '%Date ID') as [%Date ID]

Resident DA_BC;

NoConcatenate

F_DA_BC:

load *,

ApplyMap('maptable',LB) as LBopex

resident SUIVI_DA_BC;

DROP Table DA_BC;

DROP Table SUIVI_DA_BC;

NoConcatenate

F2_DA_BC:

load *

resident F_DA_BC;

drop table F_DA_BC;

drop field LB2 from F2_DA_BC;

left join (F2_DA_BC)

mapt:

LOAD

trim(lower([LB / Budget] ))as LBopex,

OpEx_Category,

trim(lower([LB / ERP])) as LB

FROM

[---]

(ooxml, embedded labels, table is Table_Correspondance);

Re: DATA not correct

swuehl — Fri, 03 Jun 2016 10:40:31 GMT

A JOIN may duplicate records, I think you already know this.

So I would suggest to check if records have been duplicated by the JOIN and then try to avoid the JOIN.

Re: DATA not correct

master_student — Fri, 03 Jun 2016 10:48:11 GMT

I have a map table that contains the LB/LBopex/opex category (excel file)

and I have a table from the data base that contains only the field LB, as a result I need to that the table F2_DA_BC

contains the LB/LBopex/opex category.

How to do that without duplicate records pb? please

Re: DATA not correct

swuehl — Fri, 03 Jun 2016 11:01:43 GMT

Does your map table contain only a unique line per LB?

If this is the case, the JOIN should not duplicate lines from your data base table.

If this is not the case, you need to decide which line to map, you can then use a MAPPING table (this ensures a unique key / value mapping without record duplication), or a JOIN with a new table only showing unique keys.

Re: DATA not correct

master_student — Fri, 03 Jun 2016 11:10:59 GMT

Yes the map table contain a unique line per LB

Re: DATA not correct

swuehl — Fri, 03 Jun 2016 11:15:04 GMT

Then double check if the number of records of your data base table has changed before vs after the JOIN.

If it hasn't changed and you are aggregating only values from the data base table, the JOIN is probably not causing the issue.

How do you calculate the amount in QV?