5 Replies Latest reply: May 15, 2014 10:12 AM by elginreigner _ RSS

    Output Help

    elginreigner _

      It's been a while since I had to do something similar to this. I have a file with an account number, record owner, date  and memo field of notes. I have parsed the notes field to 10 fields. What I need in the output is to have it listed as account number, record ower, date, note1, repeat for all 10 notes into a CSV.

       

      Example output:

       

      1,owner, date, notes1

      1,owner, data, notes2 ....

       

      Can I accomplish this with a summary?

        • Output Help
          Olly Bond

          Hello Elgin

           

          I don't think a summary would work, but you can achieve this using MCR. Define a multi column region for your detail trap with 10 columns, each with a width of 1 character, starting in position 300, or anywhere to the right of the data. Trap your data as before, and assuming your Memo field was called , then a field called defined as:

           

          Textline(Blob;Column())

           

          should give you what you need. This technique requires that the source data is treated in Monarch as a Report and not as a Database, as MCR isn't an option for databases (perhaps we could request it for a future version of Monarch?).

           

          Hope this helps,

           

          Olly

            • Output Help
              elginreigner _

              Olly

               

              Thanks for the reply. This would have probably work had each note been a set size. It range so dramatically. I ended using LSPLIT() on the note field (memo), a runtime parameter and DOS loop to achieve the correct output file.

               

              My note field: LSPLIT(NOTE0,101,'',Note_Count*2)+LSPLIT(NOTE0,101,'',(Note_Count*2)+1)

              Note_Count is my runtime parameter.

               

              Then I use the following DOS batch:

               

              SET /A VAR=1

               

              :LOOP

              IF %VAR% GTR 50 GOTO :END

               

              "c:\program files\monarch\program\monarch.exe" "I:\NEWBUSINESS\Import\Monarch Models\eSales\Notes.XPRJ" /set:NOTE_COUNT=%VAR% /PXALL

               

              SET /A VAR+=1

               

              GOTO LOOP

              :END

              /CODE

               

              This allowed me to accurately cut it up to 50 times. Originally 10 was requested, and of course changed. This way, I only have to update the LSPLIT and the 50 value in the dos command.

                • Output Help
                  elginreigner _

                  As I hit submit,  I just thought of another run time I should use. That way I would only need to update the DOS command and pass the loop count (50 in this case) as a run time and use in the LSPLIT formula.

                    • Output Help
                      Olly Bond

                      Hello Elgin,

                       

                      MCR can handle variable size notes - simply filter out those records where

                       

                      len(textline(Blob;Column()))=0

                       

                      But as you have up to 50 possible lines, then perhaps the DOS loop is the better approach, as Datawatch haven't yet increased the maximum number of columns in an MCR from the limit of 40.

                       

                      Best wishes,

                       

                      Olly

                        • Output Help
                          elginreigner _

                          Hello Elgin,

                           

                          MCR can handle variable size notes - simply filter out those records where

                           

                          len(textline(Blob;Column()))=0

                           

                          But as you have up to 50 possible lines, then perhaps the DOS loop is the better approach, as Datawatch haven't yet increased the maximum number of columns in an MCR from the limit of 40.

                           

                          Best wishes,

                           

                          Olly[/QUOTE]

                           

                          Hmm, good to know. I dont use MCR often. This is part of my current project. We are implementing a new Sales system (going cloud), and sales wants me to retain as many notes as possible.

                           

                          Without Monarch, this project would have taken me for ever to code in SQL.