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;
