topic Re: Adding calculated fields to table in QlikView

Adding calculated fields to table

Wed, 11 Mar 2015 19:53:29 GMT

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!!!

Re: Adding calculated fields to table

Anonymous — Wed, 11 Mar 2015 20:02:29 GMT

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.

Re: Adding calculated fields to table

sunny_talwar — Wed, 11 Mar 2015 20:02:46 GMT

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;

Re: Adding calculated fields to table

maxgro — Wed, 11 Mar 2015 20:03:25 GMT

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;

Re: Adding calculated fields to table

Wed, 11 Mar 2015 21:15:02 GMT

Re: Adding calculated fields to table

Wed, 11 Mar 2015 21:16:47 GMT

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;

Re: Adding calculated fields to table

Anonymous — Wed, 11 Mar 2015 21:21:28 GMT

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

Re: Adding calculated fields to table

Wed, 11 Mar 2015 21:31:41 GMT

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:

Re: Adding calculated fields to table

Wed, 11 Mar 2015 21:36:57 GMT

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/

Re: Adding calculated fields to table

Wed, 11 Mar 2015 21:39:40 GMT

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!)

Re: Adding calculated fields to table

Thu, 12 Mar 2015 11:06:24 GMT

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

Resident HistoryLinesALL;