Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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>;
...
if(DocumentType =3, fabs(QTY), if(DocumentType =4, -fabs(QTY), QTY)) as QTY,
...
Hi Manus,
create following field on Table Loading
if(DocumentType = 4, QTY*(-1), QTY) AS QTY
Regards,
Mukesh Chaudhari
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;
Hi Manus,
u want to change value at fetching time?
Then Please use SQL Syntax or function
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
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;
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
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?