2 Replies Latest reply: May 15, 2014 9:54 AM by joey RSS

    Merge fields vertically

    ozzbug _

      I have reports that are sent in a spreadsheet. I'd like to get from this:

       

      PtA   A

      PtA   B

      PtA   C

      PtB   X

      PtB   Y

       

      to this:

       

      PtA   A;B;C

      PtB   X;Y

       

       

      thanks

      jim

      Pro Ver 9

        • Merge fields vertically
          Grant Perkins

          Hi Jim,

           

          No takers among the Excel expert division so far it seems so I thought I might make a few suggestions.

           

          Depending on the nature of the data (and this is a very long shot in most cases!) you might be able to do something with a SUMMARY using the values in your second example column as an ACROSS display.

           

          Normally that would just give you a count figure but IF you can control the effective column heading to known values of the second column you can change the count to another displayed value with some success. (That said the only time I have found it useful was dealing with years where the known year number range allowed control of the values in columns using MIN or MAX measures.)

           

          A quick way would be to create a simple summary from the values just as they are and then set the summary to suppress duplicates in column one. Export the result to a report file.

           

          No you can define a template that captures the column 2 rows for each column 1 value in a single multi-row field, albeit with spaces between the values rather than ";". If you need the semi-colon spearator create a calculated field that uses the data from the column 2 extraction but replaces the spaces with ";". There are a few ways to do that with Monarch functions. (If they are not familiar to you let us know and we can suggest some options - assuming the first part of the solution appeals to you!)

           

          Hide the original column 2 and you should have the  result you need (I think).

           

          HTH.

           

           

          Grant

          • Merge fields vertically
            joey

            Essentially, you want to denormalize the database.  Denormalization is never a fun process, and the best solution I have found is to use Access.

             

            First, get the spreadsheet to Access (pick your favorite method, but Monarch will work fine). 

             

            Then, create a module with a Denormalize function.  Give it a string argument pt.  Then, it opens a cursor to the database, and reads all records that match on pt, and writes them to one string with a semicolin between each value.

             

            [font="courier"]Public Function Denormalize(pt As String) As String

             

                Dim db As Database

                Dim rst As Recordset

                Dim strSQL As String

                Dim Results As String

                  

                Set db = CurrentDb()

                strSQL = "SELECT FROM ExcelTable " & _

                         "WHERE = " & pt

                                                                                            Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

                Results = ""

                 

                If rst.RecordCount > 0 Then

                   Do Until rst.EOF

                      If (Results = "") Then

                        Results = rst!Value

                      Else

                        Results = Results & ";" & rst!Value

                      End If

                      rst.MoveNext

                   Loop

                End If

                 

                rst.Close

                Denormalize = Results

            End Function

             

              /font[/quote]Finaly, write a query that selects the unique occurances of each pt, and calls the denormalize function.

             

            Psudo SQL:

            [font="courier"]SELECT DISTINCT PrimaryKey, Denormalize(PrimaryKey)

            FROM ExcelTable[/font][/quote]This query can be imported to Monarch, exported as an Excel spreadsheet, or whatever you need.

             

            If you have a limited number of values for a given primary key (such as there can be no more than 3), then you can do it with just Monarch, but it is a multi-pass operation, and even messier than this solution (which can handle unlimited values for a given key).  I haven't come up with a better solution for denormalization in Monarch, but I'm always open to new ideas.  Let me know if you have any questions.