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

[arthur_dom]

You can do something like this:

Table:

load

QTY,

if( DocumentType = 4, -1*QTY, QTY) AS QTY_WITH_CRITERIA,

DocumentType,

<OTHER FILEDS>

FROM <WHERE THE DATA COMES FROM>;

[maxgro]

...

if(DocumentType =3, fabs(QTY), if(DocumentType =4, -fabs(QTY), QTY)) as QTY,

...

[mukesh24]

Hi Manus,

create following field on Table Loading

if(DocumentType = 4, QTY*(-1), QTY) AS QTY

Regards,

Mukesh Chaudhari

[#5]

HI All, I suspect any of the answers will work, but I get an ODBC error when adding this as an extra field in the list of loading from the ODBC error...I suspect it must be loaded 'after' the whole SQL Select connect is finished.  can you help with the "load" command to add it after the table:

My script looks like this:

ODBC CONNECT32 TO [TCC2015Q;DBQ=TCC2015Q];

NoConcatenate

HistoryLinesALL:  //data from TCC2015 database

SQL SELECT

       CaseLotCode,

       CaseLotQty,

....

       UserId,

      WhichUserDef,

    if(DocumentType =3, fabs(QTY), if(DocumentType =4, -fabs(QTY), QTY)) as SalesQty  (THIS IS WHERE I GET THE ERROR, I SUSPECT THIS COMMAND MUST BE ADDED BELOW THE "FROM" STATEMENT??)

FROM HistoryLines;

[mukesh24]

Hi Manus,

u want to change value at fetching time?

Then Please use SQL Syntax or function

[#7]

Hi Mukesh.  Not sure what you mean by that.  But basically I need the value of QTY in Qlikview to reflect correctly (positive or negative depending on DocumentType). 

Give me your opinion of the right SQL syntax and I'll try it.  Or else if you have a better idea to "add" the calculation after the SQL statement is completed, then let me have that if you think it will work better...

tx

M

[Anonymous]

Try preceeding load.

HistoryLinesALL:

load *,

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

SQL SELECT

CaseLotCode,

CaseLotQty,

....

UserId,

WhichUserDef   

FROM HistoryLines;

[jonathandienst]

Hi

You can change the expression to the correct syntax for your database, or use a preceding load like this:

HistoryLinesALL:  //data from TCC2015 database

LOAD *,

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

;

SQL SELECT

       CaseLotCode,

       CaseLotQty,

....

HTH

Jonathan

[#10]

Hi Jonathan/Greeshma,

the Load statement before the SQL Select result in "QTY field does not exist"...which I guess makes sense, as we're Loading something now BEFORE we connect with the SQL Select statement...

Any ideas?