Adding calculated fields to table

[#1]

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;

   etc....

Resident HistoryLinesALL;

Any help will be much appreciated!!!

tx

[Anonymous]

Could add one of the calculated fields you want to create to this bit :

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)

Run it and post the bit from the log file of the load statement when [if?] it fails.

[sunny_talwar]

When you are doing the left join, which fields are you left joining on?

LEFT JOIN (HistoryLinesALL)

LOAD do you have a * here????

  Date(monthstart(DDate), 'MM-YYYY') as MonthYear;

  Sum((([UnitPrice]*[SalesQty])-(([UnitPrice]*[SalesQty])*([DiscountPercentage]/10000)))) as SalesAmount;

  etc....

Resident HistoryLinesALL;

[maxgro]

change in bold

ODBC CONNECT32 TO [TCC2016Q;DBQ=TCC2016Q];

HistoryLinesALL:  //data from TCC2016 and TCC2015 database

LOAD *,

  if(DocumentType =3, fabs(Qty), if(DocumentType =4, -fabs(Qty), Qty)) as SalesQty;

SQL SELECT CaseLotCode,

    CostPrice,

    CostSyncDone,

    CustomerCode,

    DateTime,

    Date(monthstart(DDate), 'MM-YYYY') as MonthYear,

    DDate,

    Year(DDate) as Year,

    Month(DDate) as Month,

    Day(DDate) as Day,

etc

FROM HistoryLines;

ODBC CONNECT32 TO [TCC2015Q;DBQ=TCC2015Q];

Concatenate (HistoryLinesALL)

LOAD *,

  if(DocumentType =3, fabs(Qty), if(DocumentType =4, -fabs(Qty), Qty)) as SalesQty;

SQL SELECT

    CostPrice,

    CostSyncDone,

    CustomerCode,

    DateTime,

    Date(monthstart(DDate), 'MM-YYYY') as MonthYear,

    DDate,

    Year(DDate) as Year,

    Month(DDate) as Month,

    Day(DDate) as Day,

etc.

FROM HistoryLines;

LEFT JOIN (HistoryLinesALL)

LOAD

  MonthYear,

  Sum((([UnitPrice]*[SalesQty])-(([UnitPrice]*[SalesQty])*([DiscountPercentage]/10000)))) as SalesAmount

Resident HistoryLinesALL

group by MonthYear;

[#5]

[#6]

Hi Sunindia,

I've added it now, but get the error - see reply to Bill

My Join statement now looks like this:

LEFT JOIN (HistoryLinesALL)

LOAD*,

  Date(monthstart(DDate), 'MM-YYYY') as MonthYear;

  Sum((([UnitPrice]*[SalesQty])-(([UnitPrice]*[SalesQty])*([DiscountPercentage]/10000)))) as SalesAmount

Resident HistoryLinesALL;

[Anonymous]

Either remove the Sum wrapping the expression or add a Group By.

[#8]

HI Massimo, I've tried the date field in the select statement before, for some reason this doesn't work and give me this error:

[#9]

No you'll have to guide me here??  I am trying to add the "SalesAmount" and "MonthYear" fields to all the records (2 new columns).  From your comment I'm pretty sure I'm not applying the "join left" command correct and need to add existing fields on there??

let me know what you think can work here/

[#10]

Will try, now close to midnight, will let you know if it works...

Bill, does the "group by" command not cause in accurate data?  I'm trying to add a field for ever record in the existing table...SalesAmount and MonthYear must be additional fields for every record....so do I then "group by" by adding all the fields in the "group by" command???

sorry, I'm newby!

cherio (going to bed now!)