10 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Question about the function « LOOKUP » :

    lostbutterfly _

      Question about the function « LOOKUP » :

       

      I want to link two different reports by several communal information. I tried to follow the process” Creat LookUp” in the guide but I couldn’t get the right table which I was waiting for.

       

      First at all, I’m not sure that I have used the right function; and if it is the case, what shall I do for realise this operation?

        • Question about the function « LOOKUP » :
          Grant Perkins

          Hi lostbutterfly and welcome to the forum.

           

          Firstly I see you have verison 8 but is it the Standard or the Pro version?

           

          If it is the Standard version the only direct lookups possible are from the internal pre-defined lookup tables using the Calculated Field - Lookup type field feature. That may not offer you a useful solution. (However there may be some other ways to approach the problem depending upon what you can do with the reports you have but I will leave that for another post when we have worked out the options available.)

           

          If you have the PRO version you can consider using a direct EXTERNAL LOOKUP (Menu > Data > External Lookups) to a database or a suitable file.

           

          How to join your two reports will depend on what the reports look like to start with.

           

          If one is a regular report and the other is, for example, a delimited file (or a report with fixed width columns)  and you can see a way to link data from the regular report to the delimited file (i.e. there are matching fields with no need to manipulate the data to create the matches) then you could first open the regular report file and model it to get a data table. When you have the table you can define an EXTERNAL lookup directly to the delimited file and simply add the necessary extra fields from the delimited file to the existing table.

           

          However, if both of your reports are 'regular' reports (not delimited files) one of them will need to be converted to a data table to start the process. Once that is done you can export the result to a table outside Monarch (delimited file, Excel, Access, etc.).

           

          Next model your second file, create the table and make the lookup as described above in the previous example.

           

          If this sounds confusing (it is easier to demonstrate than it is to describe in words) can you provide us with some more information about the types of files you have (and the version of Monarch!) and we can develop a more specific description of the way you can solve the problem.

           

          I hope this helps you in some way.

           

           

          Grant

          • Question about the function « LOOKUP » :
            lostbutterfly _

            Thank you very much for your reply.

            I’ve the PRO version 8.0, so I can directly use EXTERNAL LOOKUP.

            I’m new to Monarch. I don’t know what are a regular report and a delimited file, could you explain them for me.

            And I’ll show you an example for clear my problem.

            I have two tables EXCEL, between them, there are information communal” eg_Number” and “N° facture”, i need by the information to combine two tables.

             

            [url="http://www.xycq.net/forum/attachments/month_0708/20070804_8af6a2a265ab36e7af10eoSuro6Uo4rD.jpg"]Attachment[/url]

             

             

            This is the description of Links,

            Links:

                 eg_Number links to eg_N?facture

             

            Imports:

                 eg_Days Lat imports to eg_Days Lat

                 eg_Due Date imports to eg_Due Date

                 eg_Balance Due imports to eg_Balance Due

             

            I can't find what's wrong in it please help me!

             

            [size="1"][ August 04, 2007, 12:04 PM: Message edited by: lostbutterfly ][/size]

            • Question about the function « LOOKUP » :
              Grant Perkins

              A 'Regular' report is the sort of thing that an application will produce - formatted with page headers, sections headers and the data usually presented in columns often with subtotals and grand totals.

               

              A de-limited file will usually have a single line per record (sometimes for long records the lines will be wrapped when presented on screen) with no specific formatting to make it easy to read.

               

              A single character, often a comma (,) or sometimes a vertical bar (| - also called a pipe character by Unix/Linux users) is used to mark the end of each field. A TAB delimited file is a variation on that idea.

               

              However, what you have seems to be two Excel table that you need to link. Monarch Pro will need to read these as DATABASES rather than 'reports'. (Maybe this is what you have already done?)

               

              Open one of the Excel tables as a Database (I would use the larger one for that as I think you have already done from the way you describe the problem).

               

              Once Monarch has read that and is able to show a table of data you need to create an EXTERNAL LOOKUP to your other Excel sheet. The link you have described looks like the correct one assuming there are no duplicates possible in the file being  looked up. I would not think you should have any problem including the fields unless for some reason the field names are already in use in the DATABASE derived table.

               

              The Link field must be the same data type and size.

               

              Based on the information you have provided it looks like you have followed the correct process so I would expect Monarch to be giving you an error message. Do you see one? If so, what does it say?

               

              Does the link fail for ALL records or only some records?

               

              From the sample screens you have provided I see that the link field is left justified. Is it a Character field?

               

              The line with number 84442129 in the "eg_Number" field appears to show the number indented by one character. If that is in fact the case the link for that record certainly will not work (unless the other table also has the key link field data indented by 1 character.)

               

              If that IS the problem you will also have difficulty making the link work in Excel if you try to make a SQL query that will try link the two tables.

               

              If none of the ideas above help you to find out what is wrong I think we need to know the error message information.

               

              I hope this helps in some way.

               

               

              Grant

              • Question about the function « LOOKUP » :
                lostbutterfly _

                Thanks a lot of for your help! and i solved the problem by following your advice.

                 

                But there're still some questions confuse me:

                 

                Firstly, as you mentioned "The Link field must be the same data type and size." what is it exactly means "same data size"

                 

                Secondly, "The line with number 84442129 in the "eg_Number" field appears to show the number indented by one character." How did the character indentify the number?

                 

                The most important question is that how can i edit the file aftre combined?

                 

                Thanks in advance!

                • Question about the function « LOOKUP » :
                  Grant Perkins

                  Originally posted by lostbutterfly:

                  But there're still some questions confuse me:

                   

                  Firstly, as you mentioned "The Link field must be the same data type and size." what is it exactly means "same data size"[/b][/quote]The number of characters in the field must be the same. If one field is 10 characters and the other 16 characters they will not match.

                   

                  [b]

                  Secondly, "The line with number 84442129 in the "eg_Number" field appears to show the number indented by one character." How did the character indentify the number?[/b][/quote]I am assuming that the data in the Excel sheet also shows the same space character at the beginning of the cell I reported above. If the Excel column is defined as TEXT (Left Justified) it is possible to enter a space character in the cell. Your example screens seem to show a space before the number.

                   

                  If the cell is defined to be a number then the character would normally be right justified and the space would not matter for the purpose of matching on the lookup.

                   

                  If the lookup for that record does not work you need to look at the data in the original Excel sheets to make a correction.

                   

                  [b]

                  The most important question is that how can i edit the file aftre combined?

                  /b[/quote]That will deend on what sort of edit you want to make.

                   

                  You can make 'changes' in the Monarch table by adding Calculated fields (for example) which use a formula to modify the data in one (or more) existing field(s) to create a new field.

                   

                  Or you can export the table (or a summary) to a new file (A character based text file or a direct export to Excel or Access for example) and work to edit that. Or even read the exported file back into Monarch for more analysis.

                   

                  There are many possibilities. It is not easy to suggest which might be best for you without knowing what you need to do for further editing.

                   

                  I hope this helps.

                   

                   

                  Grant

                  • Question about the function « LOOKUP » :
                    lostbutterfly _

                    Help! I meet the same problem as before:the data cant be linked when i perform "lookup".

                     

                    When I input some data named by number or number + alphabet (for example: 2011453D). Noly the information named by number can be expressed  but not number + alphabet.

                     

                    My step as below:

                     

                    Firstly, I Input the principal report and its table which saved as form "prn". Then continue with "data > external lookups > new, I give a new

                    name and browse the second table like a file excel, I meet my problem :

                    number can be expressed normally but number + alphabet can't be expressed.

                     

                    And when I browse the second source in dialogue "select file" it only accept file "mdb", but I haven't file "mdb". So I chose all files

                    and select that

                    file excel.

                     

                    The error message said:

                    An error was encountered while reading the sample data. The value at row 10, column 3 could not be read. This and subsequent bad values will be treated as nulls.

                     

                    I try to change the type of Field properties but seems dosent work? Please tell mw what's going wrong?

                    • Question about the function « LOOKUP » :
                      lostbutterfly _

                      In addition, may i ask that how can i quote a excel file to my report and the data still limited in the same page, you know, as the excel table is too wide and the data always be separated into two different page.

                      • Question about the function « LOOKUP » :
                        Grant Perkins

                        Hi,

                         

                        The Lookup to a database will default to a file type of mdb (An Access database lookup is assumed because that is the most common 'true' database in normal use) but, as you have found you can change to any other valid file type. The mdb extension is only set in order to list probable files. If you select the xls option instead you will only see Excel files.

                         

                        The error message for row 10, column 3 means that the value in that cell does not match the cell type of the rest of the column above it. This would normally indicate that you have a NULL or empty value or a Character based data entry in a column which is being interpreted as Numeric. It may be that most of the cells in column 3 of the Excel sheet are numeric type, but some are not.

                         

                        There are some other possibilities for this problem. You may find  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000627#000000"]this older forum post[/url] useful.

                         

                        To check this start a different Monarch session and use the Open Database option to open the Excel file directly (NOT as a lookup) to see what happens.

                         

                        You cannot change the field properties of the Excel file you are using as the lookup file in Monarch Version 8.

                         

                        If you can make changes to the Excel file you could make a new column for any columns which require that you link using CHARACTER fields and 'copy' the data from the cells in the original column to the new column, changing the data type to character. You could then link on the new column.

                         

                        Alternatively you could Export the Excel worksheet first to a fixed width or delimited file and then make you lookup link to the exported file instead of the Excel file.

                         

                        I am not sure I understand you other question. Are you viewing the information on screen or printing it to a printer (or file)?

                         

                        Can you include fewer fields?

                         

                        Can you make the columns narrower?

                         

                        Can you change the font size?

                         

                        If you are printing can you change the paper size settings on the printer 'driver'?

                         

                        I hope this helps.

                         

                        Grant

                        • Question about the function « LOOKUP » :
                          lostbutterfly _

                          According to your suggestion, I tried to open the one Excel file directly but result in a same error message "An error was encountered while

                          reading the sample data. The value at row 10, column 3 could not be read. This and subsequent bad values will be treated as nulls."

                           

                          But in anther Excel file, which with the same column,haven't this problem, i am really confused.

                          • Question about the function « LOOKUP » :
                            Grant Perkins

                            You are getting the same message so the problem is consistent and is connected to the way the interpretation engine is 'seeing' the Excel file.

                             

                            If you look at the file that has the problem USING EXCEL what is the format of the cell at row 10 column 3? What is the format of the cell at row 9 column 3 which does not give an error?

                             

                            If you also look at your other file which opens OK what are the differences between the 'good' file and the one that does not work so well?

                             

                            If you open the 'good' file in Monarch is the third column interpreted to be NUMERIC or CHARACTER?

                             

                            Are the columns in the two Excel sheets exactly the same? Or are they different but, theoretically, reporting the same data?

                             

                             

                            Grant