7 Replies Latest reply: May 15, 2014 10:11 AM by ChristD _ RSS

    External lookup field imported as character while its origin is numeric

    ChristD _

      One Monarch project exports a table across of a numeric field (2 separate and one (SUM) column as an Excel spreadsheet. These fields were imported in another Monarch model as numeric fields. These models worked perfectly for the last 9 months.

       

      Today, the model of the first project was altered so that it now contains 3 separate columns and one (SUM) column of the same numeric field and it is exported as it was done previously. But when I changed the external lookup in the second model to include the new field (column), Monarch sees the new field (column) as character field. I have tried several things to change it, but was not able to find a solution. I have tried to export it as an .mdb Access file, but also here the new column was not identified in the external lookup as a numeric field.

       

      Can you anyone help me out?

       

      Thanks,

      Christ

        • External lookup field imported as character while its origin is numeric
          Kimberly _

          Hi Christ,

           

          If a field is coming into Monarch as a character you can create a new field for the numeric value by using the calculated field function: VAL

           

          Val()

           

          svc code being the field I was changing from character to numeric.

           

          Hope this helps!

            • External lookup field imported as character while its origin is numeric
              elginreigner _

              ChristD, I know your pain. This has been one of my biggest arguments about Monarch and it's ability to 'preread' data to determine datatypes. As Kimberly stated you can create a character field.

               

              Since your issue is with an external lookout, this can create problems, especially if it a project run from a command line or inside code.

               

              To fix this issue, I have forced my data to be character and extracted what I need from the string. This will help ensure your lookups do not fail.

               

              I do have this on a future feature request, I want the ability to override the assigned data type on external looks, it would fix this issue immediately.

               

               

              FYI: Monarch reads the first 250 records of data to determine data type  (per support), to this day this is not 100%

                • External lookup field imported as character while its origin is numeric
                  ChristD _

                  Thank you both for the input. Indeed the reason is the first 250 records that Monarch uses to determine the datatype. I will now try to get the table across sorted in such a way that Monarch determines the datatype correctly, but I do hope this this is taken care of in a future feature as described above.

                   

                  Regards,

                  Christ

                    • External lookup field imported as character while its origin is numeric
                      Olly Bond

                      Hello ChristD

                       

                      I think I remember seeing a reference to a registry setting somewhere where you could tell Monarch to check more than 250 rows to determine the field type. It's a trade off between convenience and speed, I guess, as Monarch will take longer to read more records.

                       

                      If the import is as character, there's no risk of losing numeric data, as Kimberly and Elginreigner have pointed out, val() will do the job. The worse case is the opposite - imagine data with the first 250 records as numbers but with character records after that which would be interpreted as null numeric fields unless you change the field type.

                       

                      In your original case it sounds like it might be a summary export where Monarch is writing to Excel and defaults to showing key columns, even where they contain numeric data, as character fields. This makes a certain sense - some numbers are not intended to be aggregated. It doesn't make sense to add my wife's credit card number to mine, but it might make sense to add up the transactions and balances.

                       

                      If I needed to export a summary with the numeric "key" field as a measure, then it could be done in Monarch by hiding the key column and adding a measure of Unique(), which should show you the same data in Excel, but with the correct formatting when it's later read in by Excel.

                       

                      Hope this helps,

                       

                      Olly