Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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;

1 Solution

Accepted Solutions
kaushiknsolanki

Hi,

     Try this.

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

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

3 Replies
kaushiknsolanki

Hi,

     Try this.

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

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Sokkorn
Master
Master

Hi,

Try this one:

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

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

Regards,

Sokkorn

Not applicable
Author

Thank you! It works!

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