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

    Changing 'Data Length' size when doing an external lookup.

    rjgrunner _

      Hi everyone,

       

      I'm new to the forum but have used Monarch on and off over the years.

       

      I'm having a problem where I'm doing an external lookup to a text file and my data is being cut at 17 characters. Some of my data is longer (@ about 25 characters).

       

      As you can imagine this is annoying!

       

      I have a feeling that the 250 line sample doesn't have any characters over 17 - Is there anything I can do to force the Data Length size or do I have to ensure that a character with a length of 25 is in the Lookup sample?

       

      I'm using Monarch Pro vs. 9

       

      Thanks everyone!

        • Changing 'Data Length' size when doing an external lookup.
          RalphB _

          Hi and welcome to the forum.

           

          Have you tried to change the column width of the import column?

            • Changing 'Data Length' size when doing an external lookup.
              rjgrunner _

              Hi Ralph,

               

              Thanks for replying. Are you refering to the Display Width? If so I've tried that to no avail. I have the display width set at 30 characters. I've looked into the 'Link definition' and it states that the data length is 17 characters.

               

              Any help would be appreciated.

                • Changing 'Data Length' size when doing an external lookup.
                  Grant Perkins

                  Data length is the important value.

                   

                  Presumably the look up table does have values longer than 17 chars?

                   

                  Is it data that could be interpreted as numeric (therefore set to 17 digits) but is in fact character?

                   

                  If that might be the problem one option would be to include a dummy line at the top of the text file that would establish the field type and maximum size that might represent possible values for the lookup.

                   

                  There may be other ways to address this for a similar result.

                   

                  HTH.

                   

                   

                  Grant

                    • Changing 'Data Length' size when doing an external lookup.
                      rjgrunner _

                      Hi Grant,

                       

                      1) The Lookup table has values > 17 Characters.

                      2) Monarch Recognises the type as 'Character'. It is also cutting some other lines i.e. my data is 10 characters long and it is cutting it to 8 characters long.

                      3) I'll have ag o at the dummy line but there must be a 'cleaner' way of fixing this out there.

                       

                      Thanks for your input.

                        • Changing 'Data Length' size when doing an external lookup.
                          Grant Perkins

                          Hi Grant,

                           

                          1) The Lookup table has values > 17 Characters.

                          2) Monarch Recognises the type as 'Character'. It is also cutting some other lines i.e. my data is 10 characters long and it is cutting it to 8 characters long.

                          3) I'll have ag o at the dummy line but there must be a 'cleaner' way of fixing this out there.

                           

                          Thanks for your input.[/quote]

                           

                          Are we looking at a dynamic lookup table here? I assume we are or other wise you could define the lookup source file with suitable widths. You mention that the lookup is a text file (if I understood correctly) suggesting potential variability. I to would be better for consistently of it could be formatted to columns. Is it csv or tab delimited currently or some other delimiter?

                           

                          If you have a variable dynamic input you could consider preprocessing it (with Monarch for example) to standardise the lookup table format and then run the lookup against the resulting output. Two Monarch processes which can be grouped as a single activity using a batch file if required.

                           

                          Or have I completely misunderstood what your process entails?

                           

                          If you can provide examples (preferably real ones in this case) of both the primary report and the lookup input I would be pleased to take a look at it for a better understanding.

                           

                           

                          Grant

                            • Changing 'Data Length' size when doing an external lookup.
                              rjgrunner _

                              Hi Grant,

                               

                              I'm working off Excel 2003 so I'm limited to 65k lines in my excel files.

                               

                              My Text files is approx 1.2 million lines long. It lists all charges to a customer account.. I am opening this file in Monarch along with a lookup table I've created which ranks all the charges to an account i.e.

                               

                              7. Hip Procedure

                              6. CT Scan

                              5. Bednight

                              5. Bednight

                              3. Lab test 1

                              3. Lab test 2

                              1. Prosthesis

                               

                              My lookup table puts Hip procedure to the top which when I do a lookup in my other file against the customer account I get the result that the customer was in for a Hip procedure.

                               

                              To eliminate variable lengths I brought in with my Ranking lookup several columns of '¬' This is a farily unique character which I use to make my comumn split. My final text file is in a 'column' format (as I understand it)

                               

                              This text file is fine - everything is in it's right column  and no variables are truncated.

                               

                              My next step is to open a Doctor by patient account file. I want to a lookup from this file to my above text file to bring accross the Procedure & department details. Everything works ok but my procedure & department details have been truncated. Very frustrating!!!

                               

                              Thanks again Grant for your help.

                                • Changing 'Data Length' size when doing an external lookup.
                                  Grant Perkins

                                  Bear with me here, I will summarise what I interpret to be your situation.

                                   

                                  You have done some processing and have a long list of patients and procedures (in a hierarchy list) and all formatting is under control.

                                   

                                  You use the resulting table to make a further lookup to pull in, by patient, the Doctor details to identify the Doctor's department and also the procedure performed.

                                   

                                  It reads as if Monarch is assessing the size required for the results (based on the initial records at the beginning of the lookup table) and misrepresenting the potential size of the fields. If these are lookup fields from a text file I would expect you to be able to change the model (this does not seem to be a lookup field mismatch) and override the automatically assessed field width for each field to make them wide enough to bring in the entire descriptions.

                                   

                                  Have you tried that. If YES but it did not work can you give some indication of any errors reported?

                                   

                                  Grant

                                    • Changing 'Data Length' size when doing an external lookup.
                                      rjgrunner _

                                      Grant,

                                       

                                      You've summed up the problem perfectly. For whatever reason my fields were being truncated and I hadn't a clue how to 'force' the data length for my final model.

                                       

                                      I've finally figured out how to do it - It's actually quite simple - I feel daft for not knowing how to do that before!

                                       

                                      Thanks for all your help and apologies for my poor explanation of the problem.

                                        • Changing 'Data Length' size when doing an external lookup.
                                          Grant Perkins

                                          Good to hear you have a resolution for the problem. The description was fine BTW but often it is good to check understanding just to be sure that things are being described completely and nothing important is missing from the interpretation. Sometimes one can be too close to a problem to see the potential solution for a while.

                                           

                                          It's good that you have V9 for this task. Earlier version might well have required the 'first line' fix which is perfectly valid but of course much better if it can be avoided!

                                           

                                          Any time you have more questions you know the forum will be a good place to visit so I hope to see you around again. Even better if you can share your long term experience with the others here.

                                           

                                          Regards,

                                           

                                          Grant