Problems with Script

[#1]

This is my question :

I have two tables coming from 2 systems

There share the same fields except 1 (status in ERP vs. Status in Workflow)

Table A:

ERP document Number	Legal Entity code	Scanning ID	InvoiceAmt	Currency	Status IN ERP
12345	0770	DCN0001	-      317,00	EUR	Paid
12346	0770	DCN0003	5.946,31	EUR	Paid
12347	0770	DCN0004	-2068,55	EUR	On Hold
12348	0770		-411,35	EUR	Paid
12349	0770	DCN0007	-411,35	EUR	Paid

Table B:

ERP document Number	Legal Entity code	Scanning ID	InvoiceAmt	Currency	Status in Workflow
12345	770	DCN0001	317.00	EUR	Completed
12345	770	DCN0002	317.00	EUR	Rejected as it is a Duplicate
12346	770	DCN0003	-5946.31	EUR	Completed
12347	770	DCN0004	1068.55	EUR	Under Approval
Not booked in ERP	770	DCN0005	-4.12	EUR	Rejected as it is a Invalid invoice
Not booked in ERP	770	DCN0006	3937.76	EUR	Under Approval

I got the following Scenario

• It happens that due to manual typing errors some of the fields for the matching transactions (the transactions that Table A shares with the  transactions with Table B )between A&B are wrong in B so that I keep A source and just want to add only Status in Workflow
• For the transactions That are Unique for B I need to append to A all the fields form B

I got the following script that is close to what I want to get but still seems to not produce all I need ..Can you help figure out what I misss?

%keyIndex	ERP document Number	Legal Entity code	Scanning ID	InvoiceAmt	Currency	Status IN ERP	Status in Workflow
1	12345	770	DCN0001	-317	EUR	Paid	Completed
2	12346	770	DCN0003	5946,31	EUR	Paid	Completed
3	12347	770	DCN0004	-1068,55	EUR	On Hold	Under Approval
4	12348	770		-411,35	EUR	Paid
5	12349	770	DCN0007	-411,35	EUR	Paid
8	Not booked in ERP	770	DCN0006	-3937,76	EUR		Under Approval

ERP:

LOAD [ERP document Number],

[Legal Entity code],

     [Scanning ID],

     InvoiceAmt,

     Currency,

     [Status IN ERP],   

      //[Legal Entity code]&'_'&[ERP document Number]&'_'&[Scanning ID] as SCID_LE_ERP_Doc_Number,

     autonumber([ERP document Number]&[Scanning ID]) as %keyIndex

FROM

[RN Project.xlsx]

(ooxml, embedded labels, table is QWERP);

DART:

LOAD

[ERP document Number],

[Legal Entity code],

     [Scanning ID],

        [Status in Workflow],

     //[Legal Entity code]&'_'&[ERP document Number]&'_'&[Scanning ID] as SCID_LE_ERP_Doc_Number,

     autonumber([ERP document Number]&[Scanning ID]) as %keyIndex

FROM

[RN Project.xlsx]

(ooxml, embedded labels, table is QWDART);

left Join (ERP) load * resident DART;

drop table DART;

DART:

LOAD

[ERP document Number],

[Legal Entity code],

     [Scanning ID],

   num(If(InvoiceAmt Like '-*',Replace(Replace(InvoiceAmt,'-',''),'.',','),'-' & Replace(InvoiceAmt,'.',','))) as InvoiceAmt,

     Currency,

     [Status in Workflow],

     //[Legal Entity code]&'_'&[ERP document Number]&'_'&[Scanning ID] as SCID_LE_ERP_Doc_Number,

     autonumber([ERP document Number]&[Scanning ID]) as %keyIndex

FROM

[RN Project.xlsx]

(ooxml, embedded labels, table is QWDART)

where not Exists (%keyIndex);

Join (ERP) load * resident DART;

drop table DART;

[#2]

Can you upload the QVW.

SO that we can look into it and respond.

[jonathandienst]

Hi

The only problem I see at first glance is that [Legal Entity code] is in your join, but not in %keyIndex that you are using in your exists.

Secondly, your InvoiceAmt expression uses invalid syntax. There is no like statement in QV. Use WildMatch instead.

=num(If(

  WildMatch(InvoiceAmt, '-*'),

  Replace(Replace(InvoiceAmt,'-',''),'.',','),

  '-' & Replace(InvoiceAmt,'.',',')

  )

) as InvoiceAmt,

HTH

Jonathan

[#4]

Thanks for the WildMatch(InvoiceAmt function it was helpful

I have tries as well to add [Legal Entity code] to the Key %keyIndex but its even worse

my undertanding is that the statement autonumber([ERP document Number]&[Scanning ID]) as %keyIndex will crate a sequential number for each diffrent combination of ERP docuement number and Scanning ID,

so %keyIndex ID 6 & 7 should be added based on the last statmenet where not exist but it is mnot the case