3 Replies Latest reply: May 15, 2014 10:06 AM by Olly Bond RSS

    row to column data

    baghu _

      Hi,

       

      I'm importing a csv file as a database and I've provided the input and output file for your further reference.

       

      Input file

       

      SCAN#     PAGE#     code             code level

      1               7044     99213     Level 1

                                99053     Level 2

      1               7048     99213     Level 1

                                99053     Level 2

      1               7049     99214     Level 1

                                87804     Level 2

                                99058     Level 3

      1               7089     99204     Level 1

                                87880     Level 2

                                99051     Level 3

      1               7093     99204     Level 1

                                94640     Level 2

                                94640     Level 3

                                99051     Level 4

       

      Output file as required.

       

      SCAN#     PAGE#     code           code level     code 2     code 3     code 4     

      1               7044     99213     Level 1     99053                    

      1               7048     99213     Level 1     99053                    

      1               7049     99214     Level 1     87804     99058               

      1               7089     99204     Level 1     87880     99051               

      1               7093     99204     Level 1     94640     94640     99051          

       

      Whenever I see Level 2, Level 3, Level 4 etc. in the code level column, I need to copy the data under the CPT column and paste it as a new column like code 2, code 3 etc.

       

      I'm using Monarch 10.5 as the version. I do not want to export this data as a .txt file as I have another 50 columns with the same database input file.

       

      Any help is appreciated and also let me know how to attach a file in this forum.

       

      Regards

      Baghavathy

        • row to column data
          Olly Bond

          Hello baghu,

           

          This looks straightforward if you import the CSV file as a report instead of as a database. Just changing the file extension to ".prn" should tell Monarch to treat it as a text report.

           

          Then one detail template, using the floating trap, on the "Level" for the detail, and an Append template, on the 1 7084 characters. This will get the data you need into a table.

           

          After that, a summary with the Level as an across key and a measure of Unique(Value) should give you what you need. Make sure the Value field (e.g. 99051) is defined as numeric.

           

          This assumes that the value field is always numeric, that you don't need to preserve leading zeroes, and that you have one value only at each level for each code.

           

          Best wishes,

           

          Olly

            • row to column data
              baghu _

              Hello Olly,

               

              Thanks for reply.

               

              I may not be in a position to use your suggestion as once I import the data into the report window, I'll be using many functions/rules/etc. Also, as I've mentioned earlier, I may not be in a position to convert this into a .txt or .prn file as I've to convert more than 500 columns, which will be difficult.

               

              The summary window will not help me to do the calculations.

               

              Regards

              Baghavathy

                • row to column data
                  Olly Bond

                  Hello Baghavathy,

                   

                  If you have a CSV file, you can rename it as a PRN file regardless of how many columns there are. If you have more than 254 columns you won't be able to import them into Monarch in one model.

                   

                  There's nothing in the approach I proposed to stop you from adding filters and calculated fields to the data in the table window, to help you export exactly what you need from the summary window.

                   

                  HTH

                   

                  Olly