Script Question Join/concatenate

Thu, 25 Sep 2014 15:49:50 GMT

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

Re: Script Question Join/concatenate

maxgro — Thu, 25 Sep 2014 15:58:03 GMT

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

Re: Script Question Join/concatenate

Tue, 21 Oct 2014 08:04:51 GMT

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;

topic Script Question Join/concatenate in QlikView

Script Question Join/concatenate

Re: Script Question Join/concatenate

Re: Script Question Join/concatenate