Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can I add 2 if statements to the end of my loading script?
The first statement is working, but the second isn't.
When he fills COSTAMOUNTPOSTED with 0,00, he fills COSTAMOUNTADJUSTMENT with -1,00.
Can somebody help me please?
Thanks in advance,
Mirjam
--------------------------------------------------------------
FactTable_tmp:LOAD
INVOICEID,
ITEMID,
INVENTTRANSID as Invoice.INVENTTRANSID,
ApplyMap('Map_CommissionSalesGroup',Num(SALESGROUP),'Unknown') as SalesEngineer,
PRICEUNIT,
QTY,
SALESPRICE,
DISCPERCENT,
DISCAMOUNT,
LINEAMOUNTMST, SALESUNIT as SalesUnit
FROM
\\sacool03\qlikview_data$\CustInvoiceTrans.QVD (qvd)
where Left(INVOICEID,2) <> 'TF'AND Left(INVOICEID,3) <> '190'
;
LEFT JOIN (FactTable_tmp)
LOAD
INVOICEID,
ORDERACCOUNT,
SALESID,
INVOICEACCOUNT,
INVOICEDATE,
DUEDATE
FROM
\\sacool03\qlikview_data$\CustInvoiceJour.QVD (qvd)
;
InventTrans:
LEFT JOIN (FactTable_tmp)
LOAD
INVENTTRANSID as Invoice.INVENTTRANSID,
TRANSREFID as SALESID,
VOUCHER as INVOICEID,
ITEMID,
DATEFINANCIAL as INVOICEDATE,
TRANSREFID,
TRANSTYPE,
DATEFINANCIAL,
Sum(QTY) as Kostprijs.QTY,
Sum(COSTAMOUNTPOSTED) as COSTAMOUNTPOSTED,
Sum(COSTAMOUNTADJUSTMENT) as COSTAMOUNTADJUSTMENT
FROM
\\sacool03\qlikview_data$\InventTrans.QVD (qvd)WHERE
( Left(VOUCHER,2) = 'VF' OR Left(VOUCHER,2) = 'CN' )
GROUP BY
INVENTTRANSID,
TRANSREFID,
VOUCHER,
ITEMID,
TRANSTYPE,
DATEFINANCIAL
;
RetourOrder.Scrap:
LEFT JOIN (FactTable_tmp)
LOAD
RetourOrder.INVENTTRANSID as Invoice.INVENTTRANSID,
SCRAP
RESIDENT RetourOrder
;
DROP FIELD SCRAP
FROM RetourOrder;
FactTable:
LOAD
SALESID &'_'& ORDERACCOUNT &'_'& ITEMID &'_'& INVOICEDATE as %Factuur.LinkKey,
*,
If([SCRAP]='1',([COSTAMOUNTPOSTED]=0),[COSTAMOUNTPOSTED]) as CostAmountPosted.Scrap,
If([SCRAP]='1',([COSTAMOUNTADJUSTMENT]=0),[COSTAMOUNTADJUSTMENT]) as CostAmountAdjustment.Scrap
RESIDENT FactTable_tmp
;
DROP TABLE FactTable_tmp;
Hi,
Try this.
If([SCRAP]='1'0,[COSTAMOUNTPOSTED]) as CostAmountPosted.Scrap,
If([SCRAP]='1'0,[COSTAMOUNTADJUSTMENT]) as CostAmountAdjustment.Scrap
Regards,
Kaushik Solanki
Hi,
Try this.
If([SCRAP]='1'0,[COSTAMOUNTPOSTED]) as CostAmountPosted.Scrap,
If([SCRAP]='1'0,[COSTAMOUNTADJUSTMENT]) as CostAmountAdjustment.Scrap
Regards,
Kaushik Solanki
Hi,
Try this one:
If([SCRAP]='1',0,[COSTAMOUNTPOSTED]) as CostAmountPosted.Scrap,
If([SCRAP]='1',0,[COSTAMOUNTADJUSTMENT]) as CostAmountAdjustment.Scrap
Regards,
Sokkorn
Thank you! It works!
And can you tell me how the formula is if I do not rename the fields?