22 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Monarch creating extra records

    fino _

      Hi,

      I have encountered a very serious issue for which I hope there is a work around otherwise there is a serious bug in Monarch which will mean we must drop it as an application. 

      Background.

      I use an SQL database table as starting point with > 75K records.

      I import a table which has a field called "subref" - this field contains many different combinations of data, for example "a1" and "A1".

      After importing these 2 records I use an external lookup to another table in the same database to retrieve other pieces of data. After using a unique linkage the number of records increases to 4. One extra for each combination of

      a1 to A1

      a1 to a1

      A1 to a1

      A1 to A1

      I didnt think it was possible for an external lookup to create additional records since Monarch supposedly uses a left outer join? This problem becomes even greater when I import 76654 record which after adding external lookups increases to 78190 records.

      Please help

       

      Further info:

      I added the RECNO function and have same record number 1 and 2 duplicating because the last 2 digits are A1 and a1. It would appear that Monarch is not case sensitive - can anyone help or confirm please - how do I contact technical support?

       

      RECNO     ROWNO     GL_type

      1     1     02DWX000020A1

      1     2     02DWX000020A1

      2     3     02DWX000020a1

      2     4     02DWX000020a1

        • Monarch creating extra records
          Grant Perkins

          Hi fino,

           

          This is not something that I have seen before.

           

          There is a link to contact Technical Support from the Datawatch web site.

           

          http://www.datawatch.com/_support/contact_tech_support.php[/URL]

           

           

          Grant.

            • Monarch creating extra records
              fino _

              Thank you for direction

                • Monarch creating extra records
                  Grant Perkins

                  H i fino,

                   

                  My last post looks a little brusque - sorry. I was interrupted and wanted to get the post posted.

                   

                  I am wondering if there is something about running 2 SQL lookups to the same database (albeit different tables) that automatically generates more joins than just the Left Outer and if so whether there is a parameter for the sql statement that would prevent this.

                   

                  I presume that if you extract either of the tables to a text file, and then do look your up both ways from them, it all works as expected?

                   

                   

                   

                  Grant

                    • Monarch creating extra records
                      fino _

                      hi grant,

                      no your original post gave me a direction to the technical support.

                      To answer your question - no it does not give me the expected results even after taking sample data and putting into both Excel and text files. This is really concerning because when using excel and text files as both input and external lookup I get the message "the selected columns do not form a unique key to the external table" and the returning data is the first instance. I really hope the technical support team come up with a solution. We have built reports around the assumption that it can differentiate between upper and lower cases.

                      cheers

                      Fino

                        • Monarch creating extra records
                          Grant Perkins

                          hi grant,

                          no your original post gave me a direction to the technical support.

                          To answer your question - no it does not give me the expected results even after taking sample data and putting into both Excel and text files. This is really concerning because when using excel and text files as both input and external lookup I get the message "the selected columns do not form a unique key to the external table" and the returning data is the first instance. I really hope the technical support team come up with a solution. We have built reports around the assumption that it can differentiate between upper and lower cases.

                          cheers

                          Fino[/quote]

                           

                          In theory they should be case sensitive if you have set the appropriate flag in the 'Match' fields when creating the Lookup. Is this not working for you?

                           

                           

                          Grant

                            • Monarch creating extra records
                              fino _

                              Hi Grant, can you please tell me if this is V10 specific - I do not see 'Match' field checkbox anywhere. If you could give me a screen print perhaps?

                              regards

                              Fino

                                • Monarch creating extra records
                                  Grant Perkins

                                  Hi Grant, can you please tell me if this is V10 specific - I do not see 'Match' field checkbox anywhere. If you could give me a screen print perhaps?

                                  regards

                                  Fino[/quote]

                                   

                                  Hi Fino,

                                   

                                  Hmm, maybe my memory is going - sorry, I think this may only apply to the Internal Lookup facility not the external lookup - which sort of takes us back to SQL again I guess.

                                   

                                  I don't envy you with the concept of important differentiation based on upper and lower case. But I suppose the risk level depends on the nature of the database. I guess a work around might be to convert each field to ASCii character values and do the comparison that way for the lookup.

                                   

                                   

                                  Grant

                                    • Monarch creating extra records
                                      fino _

                                      hi Grant, no prob - I reckoned you were l=thinking of internal llokups alright - I actually tried a work around already using these but believe it or not when I hit the "Get input field values" it ignored the lower case field also.

                                      In relation to the ascii work around I also tried this but to no avail. My starting position is a SQL table which I can create a calculated field to hold the ascii value but the table which I need to link to via an external lookup doesnt have this value converted in the table to ascii  so I cannot link to it via the ascci code. I did thinnk about perhaps downloading the SQL table but this is very large and defeats the reason for using monarch.

                                      Still scratchin my head

                                       

                                      fino

                                        • Monarch creating extra records
                                          Grant Perkins

                                          fino,

                                           

                                          You know it's odd but when I ran a quick search for ideas it turned up this reference which SEEMS to be pretty much the opposite problem to yours.

                                           

                                          http://geekswithblogs.net/dtotzke/archive/2007/02/06/105585.aspx[/URL]

                                           

                                           

                                          Grant,

                                           

                                          also head scratching.

                                            • Monarch creating extra records
                                              Grant Perkins

                                              A thought.

                                               

                                              Are you looking up directly to the tables or to a query on the tables?

                                               

                                              Somewhere at the back of my mind something suggests this difference might be important.

                                               

                                               

                                              Grant

                                                • Monarch creating extra records
                                                  fino _

                                                  Hi Grant,

                                                  I am looking up diorectly to the tables. I did think at some time that perhaps it might be an OLE driver problem but I tried all available drivers and no luck either.

                                                  regards

                                                  fino

                                                    • Monarch creating extra records
                                                      Grant Perkins

                                                      Hi Grant,

                                                      I am looking up diorectly to the tables. I did think at some time that perhaps it might be an OLE driver problem but I tried all available drivers and no luck either.

                                                      regards

                                                      fino[/quote]

                                                       

                                                      Have you tested what happens linking to a query rather than directly to the table?

                                                       

                                                      As I mentioned there is something nagging me prompted by the observation at the end of the linked article I posted previously.

                                                       

                                                      It could, of course, be a false nag ...

                                                       

                                                       

                                                      Grant

                                                        • Monarch creating extra records
                                                          fino _

                                                          Hi Grant, I dont understand the option of linking to a query. I dont have authorisation to great a standalone executable query in MS SQL. I would understand an attempt to do this if the data source were MS Access. I should state that I have ran an SQL query to extract the data from the 2 tables directly via PL/SQL and there is no issue using a left join on the tables. My original problem still remains unfortunately in that Monarch does not recognise that the field SUBREF contains both upper and lower case characters when using the original table as input and externally looking to another table and linking on this field.

                                                          regards

                                                          fino

                                                            • Monarch creating extra records
                                                              Olly Bond

                                                              Hello fino,

                                                               

                                                              Would it perhaps help to bring in one SQL source, force the joining field to all caps, or create a flag of 1 if caps, 0 if lowercase, export to Access, repeat for the other SQL source, then join the two Access files?

                                                               

                                                              Best wishes,

                                                               

                                                              Olly

                                                                • Monarch creating extra records
                                                                  fino _

                                                                  Hi Olly,

                                                                  excellent idea only prob is that I am not allowed to use Access as an intermediary. And becasue of my record counts I cannot use Excel to do this either. Thank you for input.

                                                                  regards

                                                                  Fino

                                                                    • Monarch creating extra records
                                                                      Olly Bond

                                                                      Hi fino,

                                                                       

                                                                      In that case, you can still get that result, by outputting to CSV (delimited text format). Monarch can read these as structured data (use Open Database instead of Open Report).

                                                                       

                                                                      Best wishes,

                                                                       

                                                                      Olly

                                                                        • Monarch creating extra records
                                                                          fino _

                                                                          Hi Olly,

                                                                          I dont have the option of manipulating the data before importing into Monarch as there is a strict requirement for auditing purposes that the data come directly from the tables into Monarch and not via an intermediary. I did try to download sample data volumes in both Excel, text and csv file formats  but monarch it still links lower and upper case data. I should also pointout at this stage that the field called SUBREF can have lower or uppercase in either field position one or 2 i.e. Af, AF, af, aF therefore requiring a combination of additional toggle fields to determine which format should be used to match. In short I expected Monarch to handle upper and lower case and it doesnt appear to be able to do so reliably.

                                                                          cheers

                                                                          fino

                                                                            • Monarch creating extra records
                                                                              Olly Bond

                                                                              Hi Fino,

                                                                               

                                                                              It should still comply. Open your SQL data as normal. Create calculated fields for handling the case rules as you see fit. Export from Monarch to CSV. Repeat for the other set of data. Then join these two CSV files together in a 3rd Monarch project.

                                                                               

                                                                              Best wishes,

                                                                               

                                                                              Olly

                                                                                • Monarch creating extra records
                                                                                  Steve Caiels

                                                                                  [SIZE=3]Hi Fino,[/SIZE][/FONT]

                                                                                  [SIZE=3]Do you get the same problem with added detail rows when opening text and joining a local file (Access, Excel etc).  I’ve not seen or heard of this behaviour before and you are correct that performing a lookup should not add rows to the table. /SIZE[/FONT]

                                                                                  [SIZE=3] /SIZE[/FONT]

                                                                                  [SIZE=3]Monarch is keen to have a healthy Microsoft .Net environment.   Have you recently added or removed other applications that could upset it.  For example, installing Access 2003 on a Vista, or any machine with .Net 3.x causes a known error in the .Net Framework.  You should be able to fix any issues by installing .Net 3.5 (even if you already have it)[/SIZE][/FONT]

                                                                                  [SIZE=3] /SIZE[/FONT]

                                                                                  [FONT=Calibri]On your 2nd point, the external lookup is not case sensitive.  /FONT[/SIZE]

                                                                                  [SIZE=3] /SIZE[/FONT]

                                                                                  [SIZE=3]I guess that you are not able to open the 2 csv files in a 3rd project as OllyInMunich suggested as the csv is not a trusted source because it could be changed between exporting from Monarch and joining in a 3rd project?[/SIZE][/FONT]

                                                                                  [SIZE=3] /SIZE[/FONT]

                                                                                  [SIZE=3]However, if you open the sql data, create an upper/lower key as olly suggest using[/SIZE][/FONT]

                                                                                  /FONT

                                                                                  If(IsUpper(substr(FIELD,1,1)),"1","0")+/FONT

                                                                                  If(IsUpper(substr(FIELD,2,1)),"1","0")+/FONT

                                                                                  If(IsUpper(substr(FIELD,3,1)),"1","0") etc . . /FONT

                                                                                  /FONT

                                                                                  [FONT=Calibri]Then export the temporary table containing just the original mixed case key, the new key above and a 3rd calc field using recno()[/FONT][/FONT]

                                                                                  [FONT=Calibri]Do the same for the 2nd table.[/FONT][/FONT]

                                                                                  [FONT=Calibri] /FONT[/FONT]

                                                                                  [FONT=Calibri]Now you can open the original sql table again, so it’s compliant, join it to your new temporary table using recno(), then perform a join to the temp database for the 2nd sql table, then finally the 2nd sql table.  This way, the sql data is direct from your database.  Would that be OK?[/FONT][/FONT]

                                                                                  [/FONT]

                                                                                  [FONT=Calibri]Regards,[/FONT][/FONT]

                                                                                  [FONT=Calibri]Steve.[/FONT][/FONT]

                                                                                    • Monarch creating extra records
                                                                                      fino _

                                                                                      Gents,

                                                                                      that si the exact solution I am currentyl setting up for test - sounds like a winner - will let you all know asap

                                                                                       

                                                                                      cheers

                                                                                      fino

                                                                                        • Monarch creating extra records
                                                                                          fino _

                                                                                          Thank you all for your suggestions. I have resolved the issue by using the islower function as suggested and a few other iterations via projects. I still don't understand why Monarch doesn't have case sensitive external lookups. The solution is fine apart from all the extra work involved as we have over 150 models which in most cases have 9 external lookups. In order to ensure that this does not happen again I will pay more attention to the greater picture before beginning to design a model.

                                                                                          regards

                                                                                          Fintan

                                                                                            • Monarch creating extra records
                                                                                              Olly Bond

                                                                                              Hi fino,

                                                                                               

                                                                                              Glad it worked.

                                                                                               

                                                                                              It might be a nice feature in Monarch v11 - to be able to specify that a lookup was or was not case sensitive, or even force an override of the data type, so numeric and character fields could easily link to each other. That way, if some user opens a file in Excel, and mistakenly saves it changing a character field to a number, a Monarch lookup to that data wouldn't be broken...

                                                                                               

                                                                                              Best wishes,

                                                                                               

                                                                                              Olly