Can You explain how to concatenate Table A & Table B in QW and Later Join The result Of concatenate( Table C and D) in QW
Table A and B contain the same fields from ERP (SAP) tables but the data inside is unique in each case ( A is for Historically SAP Paid Invoices (Cleared) and B For Invoices that Are still Pending to be Paid(Open) in SAP)
Table C and D contain the same fields from a Workflow Tool used to Scan and Index Invoices prior to Booking into SAP ,but the data inside is unique in each case ( C is for Historically completed invoices (Booked in SAP or Rejected and never Booked in SAP ) and B For Invoices that Are still Pending to Approved or Solved so Parked or not registered at all in SAP)
My Logic is that I need to concatenate first A & B , then Concatenate C& D and Finally Joint the result of C&D to the Result of A&B but not sure what is the script logic I need to follow to do that
What If A & C are tables that : 1 ) I want to store in a QVD file and each month incrementally load 1 month of transactions while 2) for B and D I want them to store in QVD files but instead of incremental load I want each month to replace previously loaded data with new fresh DATA
A and C Refer to Closed items(Cleared=Paid) in two Systems (Workflow & ERP) so each month I would add to them a new month of data while B & D refer to Open (Pending Payment) transaction at a given date ( I plan to refresh each month previously loaded data with Full New Data, so no incremental load
Would that change the logic too much?
Script if needed
ERP_Cleared:
LOAD
[ECReport Period],
ERP_Closed_Line ,
[Supplier Number],
[ERP Doc Number],
//[DART DCN],
[Legal Entity code],
[PO Number],
[ERP Doc Type],
[DR/CR],
[Invoice Number],
[Invoice Currency],
[Invoice Amount (Doc Currency)],
[Invoice Date],
[Invoice Due Date],
[Invoice Due Date]-[Invoice Date] as [Payment Terms],
[Booking date],
[Payment date],
year([Payment date]) as [Year Paid],
month([Payment date]) as [Month Paid],
[Status Invoice ERP],
//[Payment Method],
if([Status Invoice ERP]<>'Paid','N/A',if( [Status Invoice ERP]='Paid',([Payment date]-[Invoice Due Date])))as [vPaid_Aging],
if(([Status Invoice ERP]<>'Paid'),'N/A',if( [Status Invoice ERP]='Paid'and ([Payment date]-[Invoice Due Date])<=0,'Paid On Time','Paid Late'))as [VPaidontime?],
if(([Status Invoice ERP]='Booked'or [Status Invoice ERP]='Booked - Blocked'or [Status Invoice ERP]='Parked'),if(([Invoice Due Date]-Today())>0,'Not Due','Due'),'N/A') as [VInvoiceDue?],
if (IsNull([ERP Doc Number]) or [ERP Doc Number]='','Not Booked in ERP' ,[Legal Entity code] & '_' & [ERP Doc Number]) as [LE_ERP Doc Number]
FROM
(ooxml, embedded labels, table is [Invoice Details-ERP-Cleared]);
ERP_Open:
concatenate (ERP_Cleared)
LOAD [EOReport Period],
ERP_Open_Line ,
[Supplier Number],
[ERP Doc Number],
//[DART DCN],
[Legal Entity code],
[PO Number],
[ERP Doc Type],
[DR/CR],
[Invoice Number],
[Invoice Currency],
[Invoice Amount (Doc Currency)],
[Invoice Date],
[Invoice Due Date],
[Invoice Due Date]-[Invoice Date] as [Payment Terms],
[Booking date],
[Payment date],
year([Payment date]) as [Year Paid],
month([Payment date]) as [Month Paid],
[Status Invoice ERP],
//[Payment Method],
if([Status Invoice ERP]<>'Paid','N/A',if( [Status Invoice ERP]='Paid',([Payment date]-[Invoice Due Date])))as [vPaid_Aging],
if(([Status Invoice ERP]<>'Paid'),'N/A',if( [Status Invoice ERP]='Paid'and ([Payment date]-[Invoice Due Date])<=0,'Paid On Time','Paid Late'))as [VPaidontime?],
if(([Status Invoice ERP]='Booked'or [Status Invoice ERP]='Booked - Blocked'or [Status Invoice ERP]='Parked'),if(([Invoice Due Date]-Today())>0,'Not Due','Due'),'N/A') as [VInvoiceDue?],
if (IsNull([ERP Doc Number]) or [ERP Doc Number]='','Not Booked in ERP' ,[Legal Entity code] & '_' & [ERP Doc Number]) as [LE_ERP Doc Number]
FROM
(ooxml, embedded labels, table is [Invoice Details-ERP-Open Items]);
DART_Completed:
// join (ERP_Cleared)
LOAD [DCReport Period],
DART_Closed_Line,
[Supplier Number],
[ERP Doc Number],
[DART DCN],
[Legal Entity code],
//[PO Number],
[DART Doc Type],
[Invoice Number],
[Invoice Currency],
//[Invoice Amount (Doc Currency)],
//[Invoice Amount Euro],
[Invoice Date],
//[Invoice Due Date],
[Scan date],
Year ([Scan date]) as [Year Scanned],
Month ([Scan date]) as [Month Scanned],
[Move To Complete Q],
[Count of Movements],
[Current Queu in dart],
if ([Current Queu in dart]='Final Reject Queue','Rejected',if ([Current Queu in dart]='Final Queue (Output)','Completed','Under Approval/resolution' )) as [Status in DART],
if (IsNull([ERP Doc Number]) or [ERP Doc Number]='','Not Booked in ERP' ,[Legal Entity code] & '_' & [ERP Doc Number]) as [LE_ERP Doc Number]
FROM
(ooxml, embedded labels, table is [Invoice Details-DART-completed]);
DART_Open:
concatenate (DART_Completed)
LOAD [DOReport Period],
DART_OPEN_Line ,
[Supplier Number],
[ERP Doc Number],
[DART DCN],
[Legal Entity code],
//[PO Number],
[DART Doc Type],
[Invoice Number],
[Invoice Currency],
//[Invoice Amount (Doc Currency)],
//[Invoice Amount Euro],
[Invoice Date],
//[Invoice Due Date],
[Scan date],
Year ([Scan date]) as [Year Scanned],
Month ([Scan date]) as [Month Scanned],
[Move To Complete Q],
[Count of Movements],
[Current Queu in dart],
if ([Current Queu in dart]='Final Reject Queue','Rejected',if ([Current Queu in dart]='Final Queue (Output)','Completed','Under Approval/resolution' )) as [Status in DART],
if (IsNull([ERP Doc Number]) or [ERP Doc Number]='','Not Booked in ERP' ,[Legal Entity code] & '_' & [ERP Doc Number]) as [LE_ERP Doc Number]
FROM
(ooxml, embedded labels, table is [Invoice Details-DART-Open Items]);
