Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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.
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
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.
Yes the map table contain a unique line per LB
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?