3 Replies Latest reply: Jul 30, 2013 6:40 AM by Mirjam Ribeiro de Oliveira RSS

    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;