6 Replies Latest reply: Feb 18, 2016 6:38 AM by Kaushik Solanki RSS

    Load Script Problem

    Christian Wylezol

      Hi,

       

      I have an issue and I dont know how to solve this.

      I have three tables. One Table with assets, one table with the different books (rules) and one table with all Transactions regarding the assets.

       

      Now I Need to define the value-field of efa_assettrans

      I want to create a new value-field called additions, but the Problem is the assetstatus (defines the Status for the whole asset, not only for Transactions, therefore it is in this table).

      The field value is only correct, when assetstatus in efa_asset is <> '1490'

       

      Now I want to say:

      if(assetstatus<>'1490'[in efa_asset], value,0) as Additions,

       

      How can this work?

       

      Thank you very much in advance.

      Chris

        • Re: Load Script Problem
          Chanty 4u

             =if(assetstatus<>'1490',[in efa_asset], value,0) as Additions,

           

           

          =Count(DISTINCT if((assetstatus<>'1490',[in efa_asset], value,0) as Additions,

          • Re: Load Script Problem
            Prashant Sangle

            Hi,

             

            try using not match() or not wildmatch()

             

            like

             

            if(not match(fieldName,FieldValue),Value,0)

             

            Regards

            • Re: Load Script Problem
              Christian Wylezol

              Hi,

               

              thanks for you suggestions but both did not work, unfortunately (or I did mistakes ).

              I will attached the qvw-file. Hopefully someone can try and solve it.

               

              Chris

               

                • Re: Load Script Problem
                  sasi k

                  The Original name of the assetstatus seems like STATUS,

                  try this code

                  IF ( not wildmatch(STATUS,'*1490*'),STATUS,0)

                  • Re: Load Script Problem
                    Sunny Talwar

                    May be this:

                     

                    MappingTable1:

                    Mapping

                    LOAD AnlagenNr,

                      assetstatus

                    Resident efa_asset;

                     

                    MappingTable2:

                    Mapping

                    LOAD #AssetBookID,

                      AnlagenNr

                    Resident efa_assetbook;

                     

                    //************************************************

                    // efa_assettrans : Anlagenbewegungen

                    //************************************************

                    efa_assettrans:

                    LOAD *,

                      If(ApplyMap('MappingTable1', ApplyMap('MappingTable2', #AssetBookID)) = 1409, 0, ApplyMap('MappingTable1', ApplyMap('MappingTable2', #AssetBookID))) as assetstatus1;

                    LOAD

                      autonumber(CMPCODE&'-'&BOOKCODE&'-'&ASSETCODE) AS #AssetBookID,

                      autonumber(CMPCODE&'-'&BOOKCODE&'-'&ASSETCODE&'-'&TXNYEAR&'-'&TXNPER&'-'&TXNDATATYPE&'-'&EVENTTYPE&'-'&RUNNUM) AS #AssetTransID,

                    // autonumber(CMPCODE&'-'&ASSETCODE) AS #AssetID,

                     

                      RUNNUM  as runnum,

                      TXNNUM  as txnnum,

                      TXNDATETIME  as txndatetime,

                    // ASSETCODE as AnlagenNr,

                    // ASSETCODE,

                      USRNAME as usrname,

                      LINKEDASSET  as linkedasset,

                      DEPNCODE as depncode,

                      BOOKCODE as Bestand_Trans,

                    // CMPCODE as cmpcode,

                      DOCCODE as doccode,

                      DOCNUM  as docnum,

                      DOCLINENUM  as doclinenum,

                      TXNDATATYPE  as txndatatype,

                      EVENTTYPE  as eventtype,

                      TXNYEAR    as txnyear,

                      TXNPER    as txnper,

                      VALUE      as  value,

                      RTRANSYR  as rtransyr, //Year, the Assets-Aktion belongs to

                      RTRANSPER  as rtransper //Period, the Assets-Aktion belongs to

                        ;

                    SQL SELECT *

                    FROM "EFA_ASSETTRANS"

                    WHERE    CMPCODE like '$(varCMPCODE_SQL)';

                     

                    store efa_assettrans into $(DirDataQVD)efa_assettrans.qvd;

                    • Re: Load Script Problem
                      Kaushik Solanki

                      Hi,

                       

                      Include below statements in your script after the store statement on efa_assettrans tab.

                       

                      Left join(efa_assettrans)

                      Load Distinct #AssetBookID , AnlagenNr as Key Resident efa_assetbook;

                       

                      Left join(efa_assettrans)

                      Load Distinct assetstatus as AssetStatus , AnlagenNr as Key Resident efa_asset;

                       

                      efa_assettrans_Final:

                      Load *,if(AssetStatus <> 1490,Value,0) as Addition

                      Resident efa_assettrans;

                       

                      Drop table efa_assettrans;

                       

                      Regards,

                      Kaushik Solanki