7 Replies Latest reply on Apr 12, 2013 9:56 PM by LarryR

    Two files with overlapping data

    user3027589
      For an interim period (3-5 months) while a new ledger system is being implemented, there will be two main files imported to HFM through FDM. Certain Entities will have data in both files which will be very tricky to handle. Essentially, they could have two identical lines of data that will need to be accumulated.

      If the first file is loaded as Replace and the second file loaded as Accumulate this should give the desired result. However, when the data needs to be reloaded this method will not work because the first file load will only Replace subcubes from that file and the second file load will accumulate on top of any unique records.

      I need to implement a custom solution that will clear out all data on the first load. There are other load locations to consider as well so just simply clearing everything won't work either.

      Any ideas or suggestions would be appreciated!
        • 1. Re: Two files with overlapping data
          LarryR
          The way I've handled this situation is to use two FDM locations, one for each file. Always force the Import of each file into FDM with the Replace option, and force the load to HFM with the Merge option. Preserve each Location's previously (and SUCCESSFULLY) loaded Export file, and with each subsequent load, append the previously loaded file sans data values to the newly Exported file. This dataless records will "clear" the previously loaded intersections for each Location from HFM. Hope this helps.
          1 person found this helpful
          • 2. Re: Two files with overlapping data
            user3027589
            Larry,

            Thanks for the response.

            Wouldn't exporting the Second file as merge just replace the overlapping values from the first file?

            For example if file 1 has:
            Entity - 1000
            Account - Sales
            Amount - 100

            and file 2 has:
            Entity - 1000
            Account - Sales
            Amount - 200

            I want the result to be 300. Loading as Merge would give me 200 (the amount of the second file).

            The last part of your explanation makes sense but I am looking for something more automated.
            • 3. Re: Two files with overlapping data
              LarryR
              OK, I think I'm not understanding your current FDM process. Are you processing file 1 from Import to target system Load, then processing file 2 from Import to target system Load, all within a single FDM POV?

              If that's the case, why not Import file 1 with the Replace option, then Import file 2 with the Append option, then Validate and Export with whatever option you deem appropriate?
              1 person found this helpful
              • 4. Re: Two files with overlapping data
                user3027589
                Sorry for not being more clear.

                I am processing both files from Import -> Export through 2 different FDM locations because the source data for both files is different. So there is no way to import them into the same FDM location.

                We are implementing a new GL system in two phases. During the interim period between the two phases the data will need to be loaded from two files. About half the entities will be in the old system and the other half being in the new for the first phase. So the files will be from two different sources and will need to be loaded into two different locations and exported separately.

                THe issue is that there is 1 or 2 entities that will have some of their data in both files. So loading the first file as replace and then loading the second file as accumulate would work until we need to reload both files. Then the second load of the first file as REPLACE would clear only its own entity data and loading the second as accumulate would result in bad data.

                Does this make more sense?
                • 5. Re: Two files with overlapping data
                  LarryR
                  I'm sure that there are other ways to do this, but the only way I can think of right now is to turn off the Load to HFM when the file 1 Location is selected in the FDM POV. You can do this in a script. I posted similar code here in the last week or two. You'll have to look up the appropriate GlobalOption identifier to disable/enable Load by Location:

                  Re: Pull adapter in FDM

                  Then after file 2 is Exported, append the contents of file 1 (sans the !Data header) to file 2 prior to the load to HFM. This can also be done in a script.
                  1 person found this helpful
                  • 6. Re: Two files with overlapping data
                    user3027589
                    May have just thought of something that answered my own question:

                    If the first file is loaded as REPLACE then it would clear the whole subcube for all entities in the file. So I would load file 1 as Replace and then file 2 as Accumulate.

                    So If I needed to reload File 2 again, I just make sure the load File 1 first as REPLACE so the whole subcube is cleared before File 2 is loaded as Accumulate.

                    Does this make sense?
                    • 7. Re: Two files with overlapping data
                      LarryR
                      Yes, it sounds like this process would work.