Hi,
I have two files that share the same fields (although one of them has more fields then the other). What I want to do i concatenate then into one table, and then add some extra columns to the new table with calculations in.
Here's a shortened version what I've tried, what happens here is that the tables are concatenated correctly, but the extra columns aren't appended added to the end. (You can ignore the functions - they all work correctly and have done for a few months - I just need to append the new data to the "CustTable" before these rules are applied). Also if you could explain why this is not working I will know for next time.
Cheers
Chris
CustTable:
LOAD *
FROM
[\\Data\cushour.txt]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
WolstTable:
Load [Planned Qty] as [Qty Ordered],
[Sched Date] as [Req Date],
[Sched Date] as [Load Date],
Bookcode,
[Qty Press],
[Qty Trim],
[Qty Edge],
[Qty Finish],
[Qty Glaze],
[Item No],
[Item Description] as Description,
Generic,
[Workorder] as [Sched Batch],
Progstage as [ProgStage],
[Qty Handov]
FROM
[\\Data\wolsthour.txt]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NewTable:
Concatenate(CustTable) Load * resident WolstTable;
LOAD*, YEAR([Load Date]) AS [LoadYear];
LOAD*, MONTH([Load Date]) AS [LoadMonth];
LOAD*, WEEK([Load Date]) AS [LoadWeek];
LOAD *, IF(FINDONEOF(UPPER([ProgStage]),'FG')>0,RANGEMAX(0,[Schedule Qty Open]-RANGESUM(RANGEMAX(IF([Qty Glaze]<=([Qty Picked]+[Qty Shipped]),0,[Qty Glaze]-([Qty Picked]+[Qty Shipped])),IF([Qty Handov]<=([Qty Picked]+[Qty Shipped]),0,[Qty Handov]-([Qty Picked]+[Qty Shipped]))))),0) as [Qty To Glaze];
LOAD *, IF(FINDONEOF(UPPER([ProgStage]),'ARWDEHIJKLMSTNOV')>0,RANGEMAX(0,[Schedule Qty Open]-RANGESUM(RANGEMAX(IF([Qty Finish]<=([Qty Picked]+[Qty Shipped]),0,[Qty Finish]-([Qty Picked]+[Qty Shipped])),IF([Qty Glaze]<=([Qty Picked]+[Qty Shipped]),0,[Qty Glaze]-([Qty Picked]+[Qty Shipped])),IF([Qty Handov]<=([Qty Picked]+[Qty Shipped]),0,[Qty Handov]-([Qty Picked]+[Qty Shipped]))))),0) as [Qty To Finish];
LOAD *, IF(FINDONEOF(UPPER([ProgStage]),'BC')>0,RANGEMAX(0,[Schedule Qty Open]-RANGESUM(RANGEMAX(IF([Qty Edge]<=([Qty Picked]+[Qty Shipped]),0,[Qty Edge]-([Qty Picked]+[Qty Shipped])),IF([Qty Finish]<=([Qty Picked]+[Qty Shipped]),0,[Qty Finish]-([Qty Picked]+[Qty Shipped])),IF([Qty Glaze]<=([Qty Picked]+[Qty Shipped]),0,[Qty Glaze]-([Qty Picked]+[Qty Shipped])),IF([Qty Handov]<=([Qty Picked]+[Qty Shipped]),0,[Qty Handov]-([Qty Picked]+[Qty Shipped]))))),0) as [Qty To Edge];
LOAD *, RANGEMAX(0,[Qty Ordered]-RANGEMAX(RANGESUM(IF([Qty Handov]<=([Qty Picked]+[Qty Shipped]),0,[Qty Handov]-([Qty Picked]+[Qty Shipped]))),[Qty Trim],[Qty Edge],[Qty Finish],[Qty Glaze],[Qty Alloc],([Qty Picked]+[Qty Shipped]))) as [Qty To Trim];
LOAD *, RANGEMAX(0,[Qty Ordered]-RANGEMAX(RANGESUM(IF([Qty Handov]<=([Qty Picked]+[Qty Shipped]),0,[Qty Handov]-([Qty Picked]+[Qty Shipped]))),[Qty Press],[Qty Trim],[Qty Edge],[Qty Finish],[Qty Glaze],[Qty Alloc],([Qty Picked]+[Qty Shipped]))) as [Qty To Press];
LOAD *, RANGEMAX(0,[Qty Ordered]-([Qty Picked]+[Qty Shipped])) as [Schedule To Pick];
LOAD *, RANGEMAX(0,[Qty Ordered]-RANGEMAX([Qty Picked],[Qty Shipped])) as [Schedule Qty Open];
LOAD *, IF([Sched and Date]<>'No Schedule Info',YEAR([SchedDate]),'') as [ScheduleYear];
LOAD *, IF([Sched and Date]<>'No Schedule Info',MONTH([SchedDate]),'') as [ScheduleMonth];
LOAD *, IF([Sched and Date]<>'No Schedule Info',WEEK([SchedDate]),'') as [ScheduleWeek];
LOAD *, [Qty Picked]*[NetPrice(Ea)] as [Value To Ship];
LOAD *, YEAR(IF([Deliv Date]<[Req Date],[Deliv Date],[Req Date])) as [ReqYrNum];
LOAD *, WEEK(IF([Deliv Date]<[Req Date],[Deliv Date],[Req Date])) +1 as [ReqWkNum];
LOAD *, RTRIM(IF([SchedDate]='','No Schedule Info',date([SchedDate],'DD/MM/YYYY') & ' - ' & [Sched Batch])) as [Sched and Date];
LOAD *,[Qty To Pick]*[NetPrice(Ea)] as [Value To Pick];
LOAD *, IF(UPPER([ItemIsStocked])='YES',RANGEMAX(0,[Qty Ordered]-([Qty Picked]+[Qty Shipped])),RANGEMAX(0,IF([Qty Handov]>[Qty Ordered],[Qty Ordered],[Qty Handov])-([Qty Picked]+[Qty Shipped]))) as [Qty To Pick];
LOAD *,[Qty To Make]*[NetPrice(Ea)] as [Value To Make];
LOAD *,(IF(UPPER([ItemIsStocked])='YES',0,RANGEMAX(0,[Qty Ordered]-([Qty Picked]+[Qty Shipped])-IF(IF([Qty Handov]>[Qty Ordered],[Qty Ordered],[Qty Handov])>([Qty Picked]+[Qty Shipped]),IF([Qty Handov]>[Qty Ordered],[Qty Ordered],[Qty Handov])-([Qty Picked]+[Qty Shipped]),0)))) as [Qty To Make];
drop table WolstTable;
