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

    External Lookup Question

    MissKim _

      Is it possible to bring back all values in the external file that match a data element in the Monarch table as opposed to the first value found? See below for an example.

       

      [U]Monarch Table[/U][/B]

      Bob   [/INDENT]Blue

      Peter   [/INDENT]Blue

      Denny   [/INDENT]Blue

      Jane   [/INDENT]Blue

       

      [U]External File[/U][/B]

      Blue   [/INDENT]123

      Blue   [/INDENT]245

      Blue   [/INDENT]896

       

      [B]Ideal Situation[/B][/U]

      Bob[INDENT][/INDENT]  Blue[INDENT][/INDENT]  123

      Bob[INDENT][/INDENT]  Blue[INDENT][/INDENT]   245

      Bob[INDENT][/INDENT]  Blue[INDENT][/INDENT]   896

      Peter[INDENT][/INDENT]   Blue[INDENT][/INDENT]  123

      Peter[INDENT][/INDENT]  Blue[INDENT][/INDENT]  245

      Bob[INDENT][/INDENT]   Blue[INDENT][/INDENT]   896

      etc.

       

      Is this possible? Please help.

       

      Please note that I tried to use the indent tags to space the info, but it keeps running all together.

       

      Thanks!:confused:

        • External Lookup Question
          Bill Watson

          can't think of any way of doing it directly in monarch as it's external lookups are built to make unique matches, rather than pull back all possibilities - think of an excel vlookup versus an access query

           

          It would be possible to build something in vb to pull in both sets of data and output a completed list with all matching combinations, but in order to do even this, each file would have to include a "key" field

           

          e.g

           

          [U]Report[/U][/B]

           

          Blue Bob

          Blue Peter

          Blue Denny

          Blue Jane

          Red Fred

          Red Bob

          /code

           

          [U]External File[/U][/B]

          Blue 123

          Blue 245

          Blue 896

          Red 987

          Red 654

          Red 321

          /code

           

          [U]VB Output[/U][/B]

          BobBlue123

          BobBlue245

          BobBlue896

          BobRed987

          BobRed654

          BobRed321

          PeterBlue123

          PeterBlue245

          PeterBlue896

          ...

          JaneBlue123

          JaneBlue245

          JaneBlue896

          FredRed897

          FredRed654

          FredRed321

          /code

           

          In general terms your vbscript would need to:

           

          Open both files in turn, reading contents into arrays

           

          Loop through "Report" array picking up Color and Name

          Loop though "External File" array picking up Color and Number

           

          Check for each "report" and "external" entry that Color from both arrays match, if so populate a third array with NameColorNumber "combination"

           

          Write out third array to a seperate text file - use this in a monarch process as your "Key file" and hang the original report/process off this list which contains all combinations even if they are present or not.

            • External Lookup Question
              Data Kruncher

              Bill, I took [I]exactly /I[/B]that approach in developing the Excel VBA code below last week.

               

              I just didn't post it then as I was still trying (unsuccessfully, in the end) to find a way with Monarch.

               

              Sub MakeTable()

                  Dim aNames() As Variant

                  Dim aColours() As Variant

                   

                  Dim iNames As Integer

                  Dim iColours As Integer

                  Dim iOutputRow As Integer

                   

                  Dim sName As String

                  Dim sColour As String

                  Dim vNumber As Variant

                   

                  iOutputRow = 0

                   

                  aNames() = Range("tblNames")

                  aColours() = Range("tblColors")

                   

                  For iNames = 1 To UBound(aNames)

                      sName = aNames(iNames, 1)

                      sColour = aNames(iNames, 2)

                       

                      For iColours = 1 To UBound(aColours)

                          If aColours(iColours, 1) = sColour Then

                              vNumber = aColours(iColours, 2)

                              iOutputRow = iOutputRow + 1

                              Range("H" & CStr(2 + iOutputRow)).Value = aNames(iNames, 1)

                              Range("I" & CStr(2 + iOutputRow)).Value = sColour

                              Range("J" & CStr(2 + iOutputRow)).Value = vNumber

                               

                          End If

                      Next iColours

                  Next iNames

              End Sub[/CODE]

                • External Lookup Question
                  Grant Perkins

                  Using Monarch alone (without programming) an approach along these lines could work provided the number of numbered codes per color is not too large.

                   

                  Firstly, using Monarch, create a new version of the External Lookup file converted and formatted like this. (CSV appearnce is just for interpretation.)

                   

                  "Blue","123 456 896"

                   

                  Export this for use as the lookup using the 'number' field as a single field array.

                   

                  Link this to the Monarch table.

                   

                  Bob Blue "123 456 896"

                  Peter Blue "123 456 896"

                  Denny Blue "123 456 896"

                  Jane Blue "123 456 896"

                   

                  " " used to denote that this will be a text field.

                   

                  Export this as a new report file (ie a Text file) with fixed format to give a columnar appearnce.

                   

                  Open the new file in Monarch and apply MCR to the template with, in the example, 3 columns for the 'numbers' field and that should provide your desired output.

                   

                  Depending in what you are working with as starting points there may be some shortcuts available to you - or there may be some as yet unidentified aspects of the data that make this approach inappropriate. However if it does work and is approproate you could also put the entire process into a batch file to be executed as a single activity once you are comortable that it all works as required.

                   

                  If the lookup file is a (relatively) fixed result (ie. it does not need to be generated dynamically every time the report is analysed) that would make things even easier since the first step would not need to be run every time. However if the lookup does change, and the changes may not be communicated in a 'timely' fashion, then running that step through the batch every time the process is run would probabaly be a safer option.

                   

                  HTH with some ideas.

                   

                   

                  Grant

                   

                   

                   

                   

                  HTH

                    • External Lookup Question
                      elginreigner _

                      I'm late to this party. Your solution via Monarch is always to start with the many results as your main project and do the lookup against the first table. Use a filter to remove any not matches, this will give you everything you need without any VB.

                       

                      I do this type of monarch projects almost all day, I deal with actions against accounts where there can be many actions but only 1 account. I will start with the action do the lookup against the account.