Script Question Join/concatenate

[#1]

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

Any help?

Thanks Ramon

[maxgro]

TAB:

load  * from A;

concatenate (TAB) load * from B;

TCD:

load  * from C;

concatenate (TCD) load * from D;

join (TAB) load * resident TCD;

drop table TCD;

you have to replace load * from ..... with the load from SAP

or post your script

[#3]

Ok Thanks a Lot …Very Very helpful

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]);

join (ERP_Cleared) load * resident DART_Completed;

drop table DART_Completed;

Drop Fields [ERP Doc Number] FROM ERP_Cleared;