9 Replies Latest reply on Mar 9, 2013 2:40 PM by 847750

    FDM sort

    847750
      Hi,

      I have a mapping file (col1, source,target) where in I need to sort records based on col1 in file.
      Can anyone help me understand how can we sort file in FDM.

      Thanks in advance

      Edited by: 844747 on Mar 4, 2013 8:48 AM
        • 1. Re: FDM sort
          847750
          Hi,

          Any suggestions..

          Thanks
          • 2. Re: FDM sort
            beyerch2
            Can you give me an idea of what you're trying to do? Where / Why do you need to sort the file in FDM? On the Import? Validation?
            • 3. Re: FDM sort
              847750
              Hi,

              Thanks for the reply.

              We have scenario where in we need to upload map in FDM by sequence number.This is for Between,In and Like maps. I've tried sequence map (by enabling sequence map in Location) using Maploader by which I am able to upload FDM maps based on sequence number perfectly.
              But the constraint is, we will receive only .trn format map files and it will hv 3 columns sequence,source,target.

              So, the challenge is I have to relate sequence column in .trn file to sequence column in FDM Maps (for Between,In & Like mappings). But, I've read that we cannot load sequence number in FDM sequence column using .trn/.tra/.tro file. That can be achieved only through excel template. Now, I'm looking for another option, where I can manipulate the original .trn file by rearranging records in sort by sequence column and then load in FDM.

              Eg: current file:

              seq,source,target

              101, test1, acc01
              105 , *, acc12
              101, test2,acc02
              104, te*, acc10
              103, tes*,acc05 where 101 is for explicit mapping and like mappings should be uploaded according to the seq 103,104,105

              Either, I should have these records loaded in FDM in the below sequence or I should hv my original .trn file to be modifed in the below format (using script) so that I can use the modified file directly in FDM.

              101, test1, acc01
              101, test2,acc02
              103, tes*,acc05
              104, te*, acc10
              105 , *, acc12

              Please let me know if there is any other approach that I can look into

              Thanks in advance
              • 4. Re: FDM sort
                beyerch2
                Perhaps there is more to your mapping layout since you're just giving an overview, but couldn't you just rewrite the existing map and then append the * at the end manually (or through a batch file / script)
                test1, acc01, Explicit
                test2,acc02, Another Explicit
                tes?,acc05, Like tes and ONLY one additional character (order does not matter)
                te?, acc10, Another like match te and ONLY one addtl character (order does not matter)
                *, acc12, Just manually append to the map you are about to load
                The 64 K question though is where is the map information coming from? Excel? SQL? By Hand? How many? 1 map or 5,000?
                If you are doing the map in excel and then creating the TRN, you could just use the Data, Sort functionality to re-order based on your segment #
                IF you are pulling from a database, you could include an order by to get them in the right order

                Hope that helps.
                • 5. Re: FDM sort
                  847750
                  Hi,

                  Thanks for quick reply.
                  I understand our requirement is making simple things complex.we can manually rearrange records as sussgested by you and load it. But,we are getting approx 25 maps in .trn format. So i don't know how feasible it is to manually manipulate records. Also, if 'te?, acc10 ' comes before 'tes?,acc05' like

                  te?, acc10

                  tes?,acc05

                  ..if 'test' comes as source member,will it not get mapped to acc10 instead of acc05 (+may be a silly question+)

                  To avoid similar kind of situation, we are opting to hv sequence number and load records sorted by seq. number

                  Thanks

                  Edited by: 844747 on Mar 6, 2013 11:56 AM
                  • 6. Re: FDM sort
                    beyerch2
                    test would get mapped to tes? because ? is ONE character, * is one to many.

                    The only thing you'd manually have to worry about is the wildcard since that MUST be last or it will take precedence over everything else. Is there no other way than to have the * at the end? For the 25 maps that are coming in, is the * line always the same or is it different for each one?
                    • 7. Re: FDM sort
                      847750
                      sorry..my mistake..it was a typo..I actually meant '*' not '?' as we don't hv '?' in or maps

                      te*, acc10

                      tes*,acc05

                      ..if 'test' comes as source member,will it not get mapped to acc10 instead of acc05.

                      Also, it is not sure that we will have source as '*' as last record in our map..It can come any where in the map.

                      Also, for the scenario like I shown above, I think we need to hv map loaded in sequence..correct me if I'm wrong

                      Thanks
                      • 8. Re: FDM sort
                        beyerch2
                        I know that your map had a * and not a ?, I was suggesting to use a ? instead of a * to help alleviate your mapping order problem.

                        I also was aware of the fact that the * mapping could be anywhere in the file. I was asking you if it's absolutely necessary to have it at all?

                        If it is absolutely necessary AND you can't train the 25 sources of the maps to force it at the end, then you don't have any out of the box options, period.

                        My opinion is that you shoudl train the 25 people to add the * at the end or give them an excel template for their mapping work that dumps it in at the end, etc.

                        I think I can solve your problem with a SQL script for EVERY map in the application in one shot, but are you comfortable running SQL scripts against the database periodically? There might be API type solution if there is an event for map importing; however, I'd have to look into that as it's not something I spend a ton of time on generally.
                        1 person found this helpful
                        • 9. Re: FDM sort
                          847750
                          Hi,

                          I could able to sort file as required.Thanks for your suggestions!!