Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Changing qty field to negative based on other criteria

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author


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

View solution in original post

13 Replies
arthur_dom
Creator III
Creator III

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

...

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

...

mukesh24
Partner - Creator III
Partner - Creator III

Hi Manus,

create following field on Table Loading

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


Regards,

Mukesh Chaudhari

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

Hi Manus,

u want to change value at fetching time?

Then Please use SQL Syntax or function

Not applicable
Author

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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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?