Hi there! I am back with another question! So I am concatenating my tables together because I was ending up with synthetic keys that were actually messing up my data. But now, the script you gave to me isn't working. It was fine up until I concatenated them, and now it's coming up with a wrong answer. Am I putting the concatenation in the wrong place?
[Aged Orders - March_temp]:
LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as TXTDate,
//Above line is for pulling date from file name
[Prom Days Out],
[Sales Doc.],
Item,
Upper(Status) as Status,
Material as %Material,
Material,
Req.dlv.dt,
[Deliv. dat],
Plnt,
[Order quantity],
[Promise Date],
[Sales Doc.] & Item as [Unique Order No.],
[Sls Org],
[Cat#],
[Material Code],
[Open Order($)],
[On Delivery($)],
[Not On Del ($)]
FROM
[\\gnrfdfp001\Purchasing\Supply Chain Reports\Aged Shortage Report By Plant\2015 Files\Aged 03*.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Aged All]);
[Aged Orders - March]:
load *,
if(([Unique Order No.])=Peek([Unique Order No.]) and [Status]<>Peek([Status]), 1, 0) as StatusChanged,
if(([Unique Order No.])=Peek([Unique Order No.]) and [Promise Date]<>Peek([Promise Date]), 1, 0) as PromiseChanged
Resident [Aged Orders - March_temp]
order by [Unique Order No.], TXTDate;
drop Table [Aged Orders - March_temp];
[Aged Orders - April_temp]:
LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as TXTDate,
//Above line is for pulling date from file name
[Prom Days Out],
[Sales Doc.],
Item,
Upper(Status) as Status,
Material as %Material,
Material,
Req.dlv.dt,
[Deliv. dat],
Plnt as Plant,
[Order quantity],
[Promise Date],
[Sales Doc.] & Item as [Unique Order No.],
[Sls Org],
[Cat#],
[Material Code],
[Open Order($)],
[On Delivery($)],
[Not On Del ($)],
[Name]
FROM
[\\gnrfdfp001\Purchasing\Supply Chain Reports\Aged Shortage Report By Plant\2015 Files\Aged 04*.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Aged All]);
[Aged Orders - April]:
concatenate ([Aged Orders - March])
load *,
if(([Unique Order No.])=Peek([Unique Order No.]) and [Status]<>peek([Status]), 1, 0) as StatusChanged,
if(([Unique Order No.])=Peek([Unique Order No.]) and [Promise Date]<>Peek([Promise Date]), 1, 0) as PromiseChanged
Resident [Aged Orders - April_temp]
order by [Unique Order No.], TXTDate;
drop Table [Aged Orders - April_temp];
