Changing qty field to negative based on other criteria

[#1]

Hi,  I'm loading data which includes a QTY (quantity) field.  The data contains another field called DocumentType.  If DocumentType = 3, then the qty must remain as a positive number, but when the DocumentType = 4, then the QTY field must be a negative.  DocumentType = 3 = Invoices.  And DocumentType = 4 = Credit Notes.  So I need to end up with a QTY field that shows the nett result of the invoices and credit notes in my charts.  NOTE:  There are also other DocumentTypes (5, 7, 8, etc.) which is in the data, but not relevant to my calculation of sales statistics.

Please assist with the load script to basically change the QTY field to a negative field for DocumentType=4.  Hope it makes sense.

tx

[Anonymous · Accepted solution]

Hi Manus,

The load statement will execute only after the SQL select. That is how preceding load works. The error is due to wrong field name. What is the field name for QTY in your database? Can you post the SQL Select script?

Regards,

Greeshma

[jagan]

Hi,

I think you should replace QTY with CaseLotQty, try below script

HistoryLinesALL:

load *,

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

SQL SELECT

CaseLotCode,

CaseLotQty,

....

UserId,

WhichUserDef  

FROM HistoryLines;

Regards,

Jagan.

[#4]

AAHAAA....the field name is Qty, not QTY.   I will correct now and revert back to you...just having and ODBC connection problem now on the server...will let you know later on

tx

M

[#5]

All sorted...thanks guys!!!