How can I add 2 if statements to my load script?

Tue, 30 Jul 2013 06:31:05 GMT

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;

Re: How can I add 2 if statements to my load script?

kaushiknsolanki — Tue, 30 Jul 2013 06:33:58 GMT

Hi,

Try this.

If([SCRAP]='1'0,[COSTAMOUNTPOSTED]) as CostAmountPosted.Scrap,

If([SCRAP]='1'0,[COSTAMOUNTADJUSTMENT]) as CostAmountAdjustment.Scrap

Regards,

Kaushik Solanki

Re: How can I add 2 if statements to my load script?

Sokkorn — Tue, 30 Jul 2013 06:49:57 GMT

Hi,

Try this one:

If([SCRAP]='1',0,[COSTAMOUNTPOSTED]) as CostAmountPosted.Scrap,

If([SCRAP]='1',0,[COSTAMOUNTADJUSTMENT]) as CostAmountAdjustment.Scrap

Regards,

Sokkorn

Re: How can I add 2 if statements to my load script?

Tue, 30 Jul 2013 10:40:09 GMT

Thank you! It works!

And can you tell me how the formula is if I do not rename the fields?

topic Re: How can I add 2 if statements to my load script? in QlikView

How can I add 2 if statements to my load script?

Re: How can I add 2 if statements to my load script?

Re: How can I add 2 if statements to my load script?

Re: How can I add 2 if statements to my load script?