Concatenate Not Working

chris1987 — Thu, 19 Jan 2017 17:17:36 GMT

Hi,

I have four tables that I'm trying to filter out and then concatenate:

MasterList: This is the master lookup sheet

NewImport: This is the information which download every time a refresh is ran

ListOfCleared: This is the final list of Items filtered Items. What I do in want in this table are all the items is filter out all the rows that exist in MasterList but not in NewImport - This works ok

Output: This is the final table which I intend to STORE, I want to concatenate UKClearedTracker.qvd with ListOfCleared where [AOrder No] & [ALine No] don't exist in UKClearedTracker.qvd, if they do then ignore them.

Everything is working up the output part, and I'm getting 3 rows, which exist in UKClearedTracker.qvd but not in ListOfCleared.

Any help would be appreciated, here's the code:

Drop tables ListOfCleared,[ListOfCleared-1],UKClearedTracker,[UKClearedTracker-1];

MasterList:

replace LOAD

DateAdded,

ID,

[Order No] & [Line No] as MasterID,

Status,

Cust,

[Cust Name],

[Cust PO],

[Ord Date],

[Load Date],

[Req Date],

[Deliv Date],

[Order No],

[Line No],

LoadDateChg,

Bookcode,

[Item No],

ItemCust,

Description,

Generic,

[Qty Ordered],

[Qty Open],

[Sched Batch],

ProgStage,

SchedDate,

[NetPrice(Ea)],

[Line-Value],

[FC Prod Hybrid]

FROM

(qvd);

NewImport:

Replace Load

today() as DateAdded,

[Order No] & [Line No] & IF(today()-[Req Date]<=7,'1to7',if(today()-[Req Date]<=14,'8to14',if(today()-[Req Date]<=21,'15to21',if(today()-[Req Date]<=28,'22to28','Over28')))) as ID,

[Order No] & [Line No] as BNewArrearsID,

Cust,

[Cust Name],

[Cust PO],

[Ord Date],

[Load Date],

[Req Date],

[Deliv Date],

[Order No],

[Line No],

LoadDateChg,

Bookcode,

[Item No],

ItemCust,

Description,

Generic,

[Qty Ordered],

[Qty Open],

[Sched Batch],

ProgStage,

SchedDate,

[NetPrice(Ea)],

[Line-Value],

[FC Prod Hybrid]

FROM

$(vUKDataFiles)Data\cushour.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where [Req Date]<today();

ListOfCleared:

add LOAD

today() as ADateAdded,

ID AS AID,

[Order No] & [Line No] as ClearedMasterListID,

Cust as ACust,

[Cust Name] as [ACust Name],

[Cust PO] as [ACust PO],

[Ord Date] as [AOrd Date],

[Load Date] as [ALoad Date],

[Req Date] as [AReq Date],

[Deliv Date] as [ADeliv Date],

[Order No] as [AOrder No],

[Line No] as [ALine No],

LoadDateChg as ALoadDateChg,

Bookcode as ABookcode,

[Item No] as [AItem No],

ItemCust as AItemCust,

Description as ADescription,

Generic as AGeneric,

[Qty Ordered] as [AQty Ordered],

[Qty Open] as [AQty Open],

[Sched Batch] as [ASched Batch],

ProgStage as AProgStage,

SchedDate as ASchedDate,

[NetPrice(Ea)] as [ANetPrice(Ea)],

[Line-Value] as [ALine-Value],

[FC Prod Hybrid] as [AFC Prod Hybrid]

Resident MasterList WHERE Not Exists(BNewArrearsID,[Order No]&[Line No]);

//So far so good

//This bit doesn't work

Output:

add load * Resident ListOfCleared;

add Load * FROM (qvd) WHERE not Exists(ClearedMasterListID,[AOrder No] & [ALine No]);

STORE Output into (qvd);

drop Tables [ListOfCleared],[MasterList],[NewImport];

drop Table Existing;

Re: Concatenate Not Working

girirajsinh — Thu, 19 Jan 2017 18:26:03 GMT

Output:

add load * Resident ListOfCleared;

add Load * FROM (qvd) WHERE not Exists([AOrder No] & [ALine No],ClearedMasterListID);

Re: Concatenate Not Working

rubenmarin — Thu, 19 Jan 2017 19:17:11 GMT

Maybe is autoconcatenating with previous table, try avoiding this with 'NoConcatenate':

Output:

NoConcatenate

add load * Resident ListOfCleared;

add Load * FROM (qvd) WHERE not Exists(ClearedMasterListID,[AOrder No] & [ALine No]);

topic Concatenate Not Working in App Development

Concatenate Not Working

Re: Concatenate Not Working

Re: Concatenate Not Working