Load statement error for calculated fields

[#1]

Im trying to load 2 calculated fields in a Load statement before my ODBC Select Statement.  If I only add the first one (calculating SalesQty), it works fine.  As soon as I add the second line, that uses the above SalesQty in its calculation, then I get an error.

Can you assist me in the script to load these two fields?  I suspect that fact that the second line depends on the first calculated field, is the problem??

Heres my script:

HistoryLinesALL:  //data from TCC2015 database

LOAD *,

  if(DocumentType =3, fabs(Qty), if(DocumentType =4, -fabs(Qty), Qty)) as SalesQty;  //This works 100% on it's own

  Sum((([UnitPrice]*[SalesQty])-(([UnitPrice]*[SalesQty])*([DiscountPercentage]/10000)))) as SalesValue; //This is where I get the error!

SQL SELECT

  CaseLotCode,

    CaseLotQty,

    CaseLotRatio,

   etc....

[fkeuroglian]

Hi Manus

you have to use Preceding  Load

HistoryLinesALL: 

load*,

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

LOAD *,

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

SQL SELECT

  CaseLotCode,

    CaseLotQty,

    CaseLotRatio,

   etc....

good luck Fernando

see this articles to understand the preceding load.

https://www.google.com.uy/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CBwQFjAA&url=http%3A%2F%2Fww...

http://www.quickintelligence.co.uk/preceding-load-qlikview/

[sunny_talwar]

This should work (Use another preceding load so that you can refer your newely created field name)

HistoryLinesALL:  //data from TCC2015 database

LOAD *,

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

LOAD *,

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

SQL SELECT

  CaseLotCode,

    CaseLotQty,

    CaseLotRatio,

   etc....

Best,

S

[maxgro]

you have a sum in the second one

so you must add a group by all other fields (the fields in load *)

example

load

     a,b,c

     sum(qty)

.....

group by a,b,c;

[#5]

HI S,

No luck now I get a new error, see below.  If I remove the extra Load statement, then it works again (only loading SalesQty).  Any advise?

[sunny_talwar]

Update: maxgro is right, you will need to add some kind of group by statement to perform the aggregation (Sum)

Try this:

HistoryLinesALL:  //data from TCC2015 database

LOAD *,

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

SQL SELECT

  CaseLotCode,

    CaseLotQty,

    CaseLotRatio,

  etc....

Join (HistoryLinesALL)

LOAD something,

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

Resident Table1

Group By Something;

Drop Table Table1;

HTH

Best,

S