1 2 Previous Next 15 Replies Latest reply on Mar 1, 2011 5:04 PM by TonyScalese

    How should I convert zero amounts to Nodata?

    811851
      Hi Guys,

      I need to load a source file in HFM via FDM to overwrite few account balances with Zeros. I had used the NZP expression in the amount field but I don’t want to load zeros in HFM. Instead I would like to replace zeros with ‘NoData’.

      The below mentioned Import(data pump) script does not work. It gives me the error- type mismatch StrField. But I think that even if it works then FDM will suppress those rows where it will find NoData because when I had loaded a file with Nodata in the amount column, FDM had suppressed those rows during import. Thoughts?

      Function XYZ(strField, strRecord)
      '------------------------------------------------------------------
      'Oracle Hyperion FDM DataPump Import Script:
      ''Created By: user
      'Date Created: 2/24/2011 10:23:30 AM
      ''Purpose: To convert the zero balances to NoData
      '-----------------------------------------------------------------
      If strField = 0000000000000.00 Then
      xyz = NoData
      Else
      xyz = StrField
      End If
      End Function


      I can also think of writing down an event script to convert the zeros to nodata after the validation or import process but I have no idea of the variables used in event scripts that store amount fields. Any guidance over here will be greatly appreciated.

      The third option could be to load zeros in HFM and then using a calculation sub routine check for the zero balances and clear them using Hs.Clear function. Please let me know your thoughts about the options mentioned above.

      Thanks in advance!
      -Kamran

      Edited by: 808848 on Feb 24, 2011 1:12 PM
        • 1. Re: How should I convert zero amounts to Nodata?
          TonyScalese
          You can't do this. FDM will only accept numeric data values.

          You can write an event or custom script to accomplish this though.
          • 2. Re: How should I convert zero amounts to Nodata?
            beyerch2
            This should do what you want

            ---------------------------------------


            Sub AftExportToDat(strLoc, strCat, strPer, strTCat, strTPer, strFile)
            '------------------------------------------------------------------
            'Hyperion FDM EVENT Script:
            '
            'Created By:      cbeyer
            'Date Created:      2011-02-24-14:48:31
            '
            'Purpose: This script will process the DAT file and replace 0 with NoData
            '
            '------------------------------------------------------------------
            'On Error Resume Next


            ' Declare local objects and variables
            Dim objFileSys
            Dim objRead
            Dim objWrite
            Dim intPos
            Dim strPath
            Dim strFileName
            Dim strTempFile
            Dim strReadText
            Dim strWriteText


            ' Create file system object used for file operations
            Set objFileSys = CreateObject("Scripting.FileSystemObject")

            'Parse File name out of full file path
            intPos = InStrRev(strFile,"\")
            strPath = Left(strFile, intPos)
            strFileName = Mid(strFile, intPos+1, Len(strFile))
            strTempFile = strPath & "TEMP_" & strFileName

            Set objWrite = objFileSys.CreateTextFile (strTempFile, True, True)
            Set objRead = objFileSys.OpenTextFile(strFile, 1, False, -1)



            'Loop through actual DAT file and wrtite data to new file that already contains clear out data
            Do While Not objRead.AtEndOfStream
                 strReadText = objRead.ReadLine
                      
                 If strReadText <> "!Data" Then
                      ' Write each line to new file
                      objWrite.WriteLine strReadText
                 else
                      'Value Row, lets find the amount and see if its zero

                      strWriteText = ""
                      For z = 1 To 12
                           strWriteText = strWriteText & DW.Utilities.fParseString(strReadText, 13, z, ";") & ";"
                      Next

                      ' For field 13, test amount and write NoData if zero
                      strField13 = DW.Utilities.fParseString(strReadText, 13, 13, ";")
                      if val(strField13) = 0 then
                           strWriteText = strWriteText & "NoData"
                      else
                           strWriteText = strWriteText & strField13
                      end if
                      ' Write the modified line with value to the new "temp" file
                      objWrite.WriteLine strWriteText


                 End If
            Loop

                 ' Close files since file I/O is complete
                 objWrite.Close
                 objRead.Close
                 
                 ' Overwrite original DAT file with the TEMP file
                 objFileSys.CopyFile strTempFile, strFile
                 
                 ' Delete the TEMP file
                 objFileSys.DeleteFile strTempFile
                 
                 ' Cleanup - destroy file objects
                 Set objWrite = Nothing
                 Set objRead = Nothing
                 Set objFileSys = Nothing

            End Sub
            • 3. Re: How should I convert zero amounts to Nodata?
              811851
              Thank you very much for the reply. I tried to run this script but it gave me an error at line no 36- Set objRead= objFileSys.OpenTextFile(strFile,1,False,-1) saying - "Invalid Procedure call or argument" I changed the line to
              Set objRead= objFileSys.OpenTextFile(strFile,1,False) but it had thrown the same error. Would you please have a look at it. Thanks in advance!
              • 4. Re: How should I convert zero amounts to Nodata?
                811851
                Sorry for this but before the error which is mentioned ablove, the script had given me an error at line no 44- If strReadText "!Data" Then. Error was - Expected 'Then'. I changed this line to If strReadText = "!Data" Then and ran this script. After tihs it gave me the error- invalid procedure call or argument which is mentioned above.
                • 5. Re: How should I convert zero amounts to Nodata?
                  beyerch2
                  I'll take a look at it later today when things slow down a little.

                  I did that off the top of my head, but I'm pretty sure it should be good.

                  Thanks
                  • 6. Re: How should I convert zero amounts to Nodata?
                    beyerch2
                    Actually.......

                    Only causes that come to mind on this error would be

                    1.) strFile is null or not a valid file name.
                    2.) The user that is executing the code does not have proper permissions to see/access the file.

                    I would ensure this code is running after the export?
                    • 7. Re: How should I convert zero amounts to Nodata?
                      TonyScalese
                      A reason why sharing scripts via the forum is a dangerous activity. I would bet that this script was created as a custom script and not an event script.
                      • 8. Re: How should I convert zero amounts to Nodata?
                        beyerch2
                        I find nothing about this discussion remotely dangerous.

                        If the main goal of the forum is to share knowledge this would include script code (custom or not).

                        The only danger would be people taking things and implementing them into a production environment without fulling understanding them first.

                        With that said; however, learning is a process of trial and error. (mostly error :) )

                        $.02
                        • 9. Re: How should I convert zero amounts to Nodata?
                          TonyScalese
                          By danger, I meant you are going to spend your valuable time figuring out why the script that you shared which presumably works for you does not work for someone else. If you have the free time to do that, all the power to ya :)

                          I can't share scripts as they are the property of the companies that have paid for me to write them but you may not have that restriction.

                          Edited by: TonyScalese on Feb 25, 2011 12:17 PM
                          • 10. Re: How should I convert zero amounts to Nodata?
                            beyerch2
                            I'll put as much time into the debugging as I put into the writing of the script in the first place. :)
                            As I wrote it for the purpose of that post, I don't have to worry about any restrictions on it; however, I think I want to do something similar for a budget FDM app in the future, so I'll probably re-use part of it there.
                            • 11. Re: How should I convert zero amounts to Nodata?
                              TonyScalese
                              Presumably you will be using multiload for the budget? Just be aware that depending on the method you use (XL v txt), you may have different file formats created by FDM. Something to think about when you modify your script.
                              • 12. Re: How should I convert zero amounts to Nodata?
                                beyerch2
                                I've used the multi-load for the last two budget cycles now and we load via XLS. The issue I'm debating on cleaing up is zero amounts are sneaking in throw some of the XLS files. I'm debating on just cleaning it up during the export stage as cleaning up the DAT files seems like the least effort for me. Of course my opinion might change when I get serious about updating this! :-)
                                • 13. Re: How should I convert zero amounts to Nodata?
                                  TonyScalese
                                  Are you loading to HFM or Essbase?
                                  • 14. Re: How should I convert zero amounts to Nodata?
                                    beyerch2
                                    Tony,

                                    We are using HFM.... The HFM implementation here is a swiss army knife. :) :( :0 :)
                                    1 2 Previous Next