Hi, I'm battling to add multiple calculated fields (some of them depends on the previous calculated field) to my table:
Heres the data with comments:
ODBC CONNECT32 TO [TCC2016Q;DBQ=TCC2016Q];//data from TCC2016 database
LOAD *,
if(DocumentType =3, fabs(Qty), if(DocumentType =4, -fabs(Qty), Qty)) as SalesQty; (I managed to at least load this field, but get errors when I try to do more here)
HistoryLinesALL: //data from TCC2016 and TCC2015 database
SQL SELECT CaseLotCode,
CostPrice,
CostSyncDone,
CustomerCode,
DateTime,
DDate,
Year(DDate) as Year, (I managed to add these 3 date-related fields at least!)
Month(DDate) as Month,
Day(DDate) as Day,
etc
FROM HistoryLines;
ODBC CONNECT32 TO [TCC2015Q;DBQ=TCC2015Q]; (Data from second database...)
Concatenate (HistoryLinesALL)
LOAD *,
if(DocumentType =3, fabs(Qty), if(DocumentType =4, -fabs(Qty), Qty)) as SalesQty;
SQL SELECT
CostPrice,
CostSyncDone,
CustomerCode,
DateTime,
DDate,
Year(DDate) as Year,
Month(DDate) as Month,
Day(DDate) as Day,
etc.
FROM HistoryLines;
Now here I want to add more fields, but it's just not working (I tried preceding load as well), such as:
LEFT JOIN (HistoryLinesALL)
LOAD
Date(monthstart(DDate), 'MM-YYYY') as MonthYear;
Sum((([UnitPrice]*[SalesQty])-(([UnitPrice]*[SalesQty])*([DiscountPercentage]/10000)))) as SalesAmount;
Hi Bill, when I remove the "sum" I get zero value through...but I am not adding any fields or group by function..only the calculated fields. I suspect I'm simply not understanding the Join function...can you help. Here is my script at this stage:
LEFT JOIN (HistoryLinesALL)
LOAD*,
Date(monthstart(DDate), 'MM-YYYY') as MonthYear,
([UnitPrice]*[SalesQty])-(([UnitPrice]*[SalesQty])*([DiscountPercentage]/10000)) as SalesAmount
