15 Replies Latest reply: May 15, 2014 10:02 AM by Data Kruncher RSS

    Multiple Lines Onto One Line

    JTR1104 _

      I used Monarch v3 years ago to pull data off of financial reports that could not be retrieved using other tools.   For the last 2 weeks I've been trying to use Monarch to resolve a predictive dialing problem I have.  This office is moving to a new state system and reporting will be controlled by the state so realtime querying for data will not be available.

       

      So, I feel that Monarch is a good long term solution for data mining these reports and slicing and dicing the data.    This forum has also been a great source of answers to a lot of questions I've had.   Also, I am using Monarch Pro V6.01.

       

      Here is my predictive dialing problem.    The state has created a report that has the information needed but the data is provided as follows:

       

      Case#   LName        FName     Phone#             Status      Type     Lang

      11111    Smith         Joe         555-555-1234      Pri           HOM     ENG

      11111    Smith         Joe         555-555-2345      Sec         WRK     ENG

      11111    Smith         Joe         555-555-3456      Sec         MBL      ENG

      11111    Smith         Joe         555-555-4567      Sec         HOM     ENG

      22222    Vasquez      Bob        555-666-2345       Pri          HOM      SPN

      22222    Vasquez      Bob        555-666-1234       Sec        WRK      SPN

      22222    Vasquez      Bob        555-666-2345       Sec        HOM      SPN

       

      What the predictive dialing system is expecting is:

       

      11111,Smith,Joe,555-555-1234,555-555-2345,555-555-3456,ENG

      22222,Vasquez,Bob,555-666-2345,555-666-1234,,SPN

       

      Is this possible?   If not possible with v6.01, maybe a newer version?   Any suggestions?

       

      I can almost get there if the Join was a little more flexible allowing joining to a table/database and using the if function.

       

      I also want to make this as automated as possible so it takes the state file and does what is needed with little to no human intervention.

       

      Any ideas, advise, etc. will be much appreciated.

        • Multiple Lines Onto One Line
          JTR1104 _

          One last thing ... the state provides this particular report as a CSV file so when it is first opened in Monarch the fields do not always line up because the length of the LName, FName, and Case# may vary.

            • Multiple Lines Onto One Line
              Data Kruncher

              OK, JTR, I have a solution, but it takes a few steps to get there, so go get yourself a fresh coffee or other stimulant before we dig in.

               

              Ok then.

               

              This will require a two part process. The first part generates a lookup table for the various phone numbers for each case number. The second part will integrate those numbers with the original data and create your final output file.

               

              On with the first part then.

               

              First, v9 Pro can read the csv file as a database, so that would make the original input is really easy. Not sure about v6.

               

              Next up, we need separate fields for HomeNumber, WorkNumber and MobileNumber.

               

              HomeNumber:

              if(Type="HOM",Phone,"")[/CODE]

               

              and similar WorkNumber:

              if(Type="WRK",Phone,"")[/CODE]

               

              and finally MobileNumber:

              if(Type="mbl",Phone,"")[/CODE]

               

              Now it looks to me like your output rules ignore the secondary home number, so let's create a filter to ignore those:

              .Not. (Type="HOM" .And. Status="Sec")[/CODE]

               

              Now we have the basic information. We'll build a summary to help with the next part of the job.

               

              Use Case, HomeNumber, WorkNumber, MobileNumber and Type as key fields. Use count as the only measure. V9 nicely allows you to hide the count, as you don't really need it, but in v6 include it and subsequently ignore it. No problem.

               

              Be sure to specify that the summary will use the exclusion filter we defined earlier.

               

              Build a project export to send the summary to an Excel file. Take note of the export location and filename. Save the model. I used "Generate Phone List". Now save the project file. Again, I used the same name for the project.

               

              Now close everything and reopen the data file and the model you just saved. In the Table window, delete all three of the calculated fields we built for the phone numbers. Good.

               

              Now here's the trick that makes your end result possible. Instead of trying to include all three phone number in one external lookup, create a separate lookup for each one.

               

              But because we're going to filter out some of the records as part of the lookup, which is possible with v9, I'm not sure if this entire solution is possible for you with v6. Try and see what happens anyway.

               

              Right, on to the first lookup for the Home number.

               

              Point to the exported summary from the first step. I applied a filter, Type="HOM". The source column and the table window column is the Case field. Select the HomeNumber field.

               

              Now build two more for each of WorkNumber and MobileNumber. This gives you all of the proper numbers within each record. Nice.

               

              Now you can hide a few fields, re-sequence them as necessary and export a final output csv file. Or you can build a single calculated field for the output, and hide the rest:

              trim(str(case))","lname","fname","homenumber","worknumber","mobilenumber","language[/CODE]

               

              Save this model and project as "Final Phone List".

               

              Now that you have two project files it's a pretty straightforward exercise to string them together with a simple batch file. The first creates your lookup table, the second reads the table and creates the final output file.

               

              Hopefully I didn't overlook any steps in my description.

               

              Again, I'm not sure that you can accomplish this with v6 with the same directions, but it didn't take too much work to get it done with v9.

               

              HTH,

              Kruncher

                • Multiple Lines Onto One Line
                  JTR1104 _

                  Thanx Kruncher,  that was quick.

                   

                  I will print off and try out your solution.   But a few questions first.

                   

                  Not sure how to generate a lookup table.  I wanted to create a second copy of the file to be used as a lookup table but it seems that a lookup table (at least in v6.01) is something more like a drop down list to chose from that is maintained manually.  I need to create the lookup table each time I process the data and join to it

                   

                  Are the 3 created fields (HomeNumber, WorkNumber, MobileNumber) new calculated fields in the table?

                   

                  From what I have read, v6.01 does not have the Not capability.  However, I cannot ignore the secondary home number.   What I need to be able to do is end up with three numbers - Primary and two secondaries and no duplicates.  Unfortunately, how the numbers are entered is up to a human.

                   

                  Like I said I will work through your suggestion and see how far I get.   Upgrading to v9.0 is option as is hopefully submitting more questions.

                   

                  Thanx again for your prompt and detailed reply.

                    • Multiple Lines Onto One Line
                      Data Kruncher

                      You're slightly confusing [URL="http://********************/calcfield/comparing-monarchs-internal-and-external-lookups"]internal lookup fields and external lookups[/URL]. That's pretty common. As you have v6 Pro, you should be able to create external lookups.

                       

                      Including the secondary numbers wouldn't be too much of a problem. You'd just need to create three more fields - SecHome, SecWork, and SecMobile:

                      if(Status="Sec" .and. Type="HOM",Phone,"")[/CODE]

                       

                      and change the original phone number fields to be primary fields PriHome etc.

                      if(Status="Pri" .and. Type="HOM",Phone,"")[/CODE]

                       

                      Of course this will all entail more fields in the summary and maybe[/B] more sophisticated lookup keys than the simple Case number.

                       

                      As for the .Not. operator, you could just adjust the formula slightly, but since you don't want to filter out the secondary stuff anyway, we'll skip that.

                       

                      As to the duplicates, that I did overlook in my reply. v9 allows for filtering duplicates on the Advanced tab of the filter dialog. I selected to include only one row for those rows with duplicated Case numbers.

                       

                      At this point I suspect that you're headed for an upgrade of your software. Not a bad thing at all. :cool: There are plenty of other benefits in store for you that are well documented elsewhere.

                       

                      Kruncher

                        • Multiple Lines Onto One Line
                          JTR1104 _

                          Thanx again Kruncher,

                           

                          I am working through your suggestion and have ordered v9.01.

                           

                          In the meantime, is there a way to create and reset a counter for each primary sort key? For example:

                           

                          Case# LName FName Phone# Status Type Lang CaseCTR

                          11111 Smith Joe 555-555-1234 Pri HOM ENG 1

                          11111 Smith Joe 555-555-2345 Sec WRK ENG 2

                          11111 Smith Joe 555-555-3456 Sec MBL ENG 3

                          11111 Smith Joe 555-555-4567 Sec HOM ENG 4

                          22222 Vasquez Bob 555-666-2345 Pri HOM SPN 1

                          22222 Vasquez Bob 555-666-1234 Sec WRK SPN 2

                          22222 Vasquez Bob 555-666-2345 Sec HOM SPN 3

                           

                          Thanx

                            • Multiple Lines Onto One Line
                              Data Kruncher

                              A little while ago, [URL="http://www.monarchforums.com/showthread.php?p=10020#post10020"]Gerhard had a great solution for a similar challenge[/URL], but his solution doesn't seem so well suited to you needs because he employed a trick with a page header template which I don't think is available to you.

                               

                              But I would expect that my Excel based solution in the same post would work just fine for your requirements. Again, it is a little bit more work, but will accomplish what you want.

                               

                              Kruncher

                                • Multiple Lines Onto One Line
                                  JTR1104 _

                                  Too bad the count() function does not allow entering a sort key as in

                                  count(Case#).   Used to have it in SQL.

                                   

                                  You've given me lots of ideas and am just getting into v9.01.    I will play around and experiment.

                                   

                                  Thanx again.

                                    • Multiple Lines Onto One Line
                                      JTR1104 _

                                      Kruncher,  I have been playing with v9.01 and it has some nice features.  However, in following your advise, I am confused with the following:

                                       

                                      Use Case, HomeNumber, WorkNumber, MobileNumber and Type as key fields. Use count as the only measure. V9 nicely allows you to hide the count, as you don't really need it, but in v6 include it and subsequently ignore it. No problem.

                                       

                                      Be sure to specify that the summary will use the exclusion filter we defined earlier.

                                       

                                      Build a project export to send the summary to an Excel file. Take note of the export location and filename. Save the model. I used "Generate Phone List". Now save the project file. Again, I used the same name for the project.

                                       

                                      Now close everything and reopen the data file and the model you just saved. In the Table window, delete all three of the calculated fields we built for the phone numbers. Good.

                                       

                                      Now here's the trick that makes your end result possible. Instead of trying to include all three phone number in one external lookup, create a separate lookup for each one.

                                       

                                      But because we're going to filter out some of the records as part of the lookup, which is possible with v9, I'm not sure if this entire solution is possible for you with v6. Try and see what happens anyway.

                                       

                                      Right, on to the first lookup for the Home number.

                                       

                                      Point to the exported summary from the first step. I applied a filter, Type="HOM". The source column and the table window column is the Case field. Select the HomeNumber field.

                                       

                                      Now build two more for each of WorkNumber and MobileNumber. This gives you all of the proper numbers within each record. Nice.[/QUOTE]

                                       

                                      An external lookup requires a unique key.  If I use the functionality to remove duplicates based on Case#, I get one line per Case# with one type.  When I do the external lookup I have to include Case# and Key to link to the external Excel lookup table.   It always finds the summary record with the key having the same Case# and Type found in the one source record.   Cannot understand how filtering can help.

                                       

                                      I am obviously missing something here.   Confused!!!

                                        • Multiple Lines Onto One Line
                                          Data Kruncher

                                          I believe that the confusion comes because of the vague use of the term "filter" I used. This is not the common Table window filter, but rather applies to the third step of the External Lookup wizard. In that dialog there's an option for "Source Filtering". First click the "Apply filter" box, the click the "Change..." button.

                                           

                                          That's where I built the type filter.

                                           

                                          But because you want a Primary and two secondary numbers, that complicates it ever so slightly. Is the Primary always the Home number? Or is it possible that you have many combinations to deal with, such as the Work or Mobile is the Primary number? That may mean six different lookups for Primary and Secondary Home, Work and Mobile numbers, each of which has a "source filter" expression similar to:

                                          [SIZE=2]Status="Pri" .And. Type="HOM"[/SIZE][/CODE]

                                           

                                          Then I imagine that you'd need a collection of calculated fields with some kind of nested If's to determine the final Primary and Secondary numbers to use in your final output.

                                           

                                          Does this help you to proceed a little?

                                            • Multiple Lines Onto One Line
                                              Grant Perkins

                                              Well, now you have 9.01 ....

                                               

                                              If you are no taverse to a 2 step process (can be batched into a single task if this is a regular activity) you could try this.

                                               

                                              Read the file as a database.

                                               

                                              Create a summary that looks like this

                                               

                                              Case# LName FName Phone#       Type  Lang Status

                                              11111 Smith   Joe 555-555-1234 HOM   ENG Pri

                                                                555-555-2345 WRK   ENG Sec

                                                                555-555-3456 MBL   ENG Sec

                                                                555-555-4567 HOM   ENG Sec

                                              22222 Vasquez Bob 555-666-1234 WRK   SPN Sec

                                                                555-666-2345 HOM   SPN Pri

                                                                555-666-2345 HOM   SPN Sec

                                               

                                              /code

                                               

                                              Obviously the field need to reflect the max widths you will have in the report.

                                               

                                              The first 3 columns are set to 'Suppress Duplicate Values' in the summary (See Display Tab for the field in question.)

                                               

                                              Now export that as a fixed width output (use a fixed font to make life easier).

                                               

                                              This is a new version of the report that can be modelled making the phone number fields a single multi-line field. Your original output spec only required a list of numbers, type not important, so I'll stick to that for now. Makes it a little simpler at this stage.

                                               

                                              In the resulting table make a calculated field using the TEXTLINE() function with the LINE NUMBER option to create Phone 1 , Phone 2 etc. Order the columns as required, hide those you don't need.

                                               

                                              Export the result as a csv file ...

                                               

                                              By no means the only option nor sophisticated but it is quick and simple and assumes that the original list was in order of preference.

                                               

                                              Could do more if required.

                                               

                                              HTH.

                                               

                                               

                                              Grant

                                                • Multiple Lines Onto One Line
                                                  JTR1104 _

                                                  Thanx Kruncher and Grant,

                                                   

                                                  First,    For each Case# there can be multiple phone records.   To complicate it, there is usually 1 PRI Status Record and multiple SEC records.  However, there does need to be a PRI record, can be just multiple SEC records.    Which is why Grant's assumption that the order of records in the list is in the order of preference is correct.   First record always has the primary phone number whether a PRI or SEC status.

                                                   

                                                  Also, there can be multiple records with the same TYPE.   To complicate things (due to human input of the data as explained before) phone numbers can be duplicated (eg.  PRI HOM Phone# can be same as a SEC HOM Phone#).   I think I can clean this up later.

                                                   

                                                  In order to simplify things and as explained above, the sequence of the records is the order of preference.   I need the top 3 or less phone numbers in one record for the Case#.   (eg. one case may have 1 phone record, another case 2 phone records, another case 3, and yet another 6 phone records.

                                                   

                                                  When I try the external lookup, I get hung up on the unique key.  I used the "Source Filter" and the correct records pop up on the subsequent screen but not in the end result.

                                                   

                                                  I tried Grant's suggestion and can get Phone1 but Phone2 and Phone3 are blank.   I am sure (hopefully) it is because I am not sure how to use the TEXTLINE function.   I am using TEXTLINE(PhoneNum,1) which gets Phone1 but TEXTLINE(PhoneNum,2) for Phone2 gets blanks.

                                                   

                                                  FYI, the predictive dialing system reads in the record for the case and calls Phone1 (primary).  If no answer, uses Phone2 (alternate). If no answer, uses Phone3 (alternate). 

                                                   

                                                  I hope this helps.   I am really grateful for your suggestions.  I am still learning Monarch and lots has changed since v3.0, even since v6.01.  Your help is great.

                                                   

                                                  JTR1104