7 Replies Latest reply: Jul 26, 2018 11:11 AM by Al Rice RSS

    How to Model Flat Invoice Records in Excel File

    Al Rice

      Hello All:

       

      I have a situation that is a first for me.  I downloaded an Invoice file as Excel File, and each invoice is showing as one row, with the billing line items (SKU#, Qty, Unit Price, Line Total) is showing as columns.   As such, each invoice varies in terms of the number of columns. Could someone help me to create a Monarch Model for such Excel file, to write meaningful reports against the data.

       

      I have been successful trapping such text/pdf reports, but never encountered Excel files like this before.

       

      Many thanks in advance.

       

      Al Rice

        • Re: How to Model Flat Invoice Records in Excel File
          Steve Caiels

          Hi Al,

           

          Good to hear from you again!

           

          If you have Data Prep Studio, It sounds like the newer implementations of UnPivot will do what you need.

           

          It can turn data like this . . . .

           

          Fixed1    Fixed2      Fixed3    LineItem1a   LineItem1b   LineItem1c  LineItem2a   LineItem2b   LineItem3c  etc d   etc e

          abc       xyz         def       aaa          bbb          ccc         ddd          eee          fff         xxx     yyy

          123       789         456       111          222          333                                               555


          (where 'etc d' and 'etc e' are full sets of LineItem data)


          into this

           

          Fixed1    Fixed2      Fixed3    LineItem a   LineItem b   LineItem c

          abc       xyz         def       aaa          bbb          ccc        

          abc       xyz         def       ddd          eee          fff        

          abc       xyz         def       xxx          xxx          xxx

          abc       xyz         def       yyy          yyy          yyy

          123       789         456       111          222          333

          123       789         456      

          123       789         456       555          555          555

          123       789         456      

           

          Of course, you can then filter out the empty rows.

           

          If you do not have Data Prep Studio, the Multi Colum region may help.  But it is not available for Excel inputs, so you would need to either export it from Excel as a Space Delimited text file, ensuring all the line item columns were consistent lengths. Or, take the Excel fine into Monarch 'as is', fix the column widths, then Export the table to a fixed width text file, then bring it back into a second Monarch model to use the Multi Column Region.


          Regards,

          Steve.

            • Re: How to Model Flat Invoice Records in Excel File
              Al Rice

              I do have Data Prep Studio.  I am stumped.  When I try to use Unpivot in Transform Data, and select the columns to unpivot, the "Ok" button is grayed  out and wont allow me to complete the process.  What am I doing wrong.

               

              Please Help

              Al

                • Re: How to Model Flat Invoice Records in Excel File
                  Chris Porthouse

                  What version of Data Prep are you using?  Make sure when you select the columns to unpivot, you are clicking on the "key" icon:

                  When it turnsd dark blue, it will be moved from the attribute column section to the unpivot column section.  You will then need to choose the check boxes in the attribute column section for the columns you want included in the output unpivot table.

                   

                  If you don't see the "Ok" button, try resizing the unpivot dialog box.

              • Re: How to Model Flat Invoice Records in Excel File
                Chris Porthouse

                In additions to Steve's suggestion (which is probably going to be the easiest), if someone does not have Data Prep Studio, you can try printing/saving the Excel file as PDF and then try capturing your data with report design and templates.

                • Re: How to Model Flat Invoice Records in Excel File
                  Al Rice

                   

                  Thanks Steve and Chris:

                   

                  I am using the latest Data Prep version 15.1.  I am making great progress, but I still cannot determine how to get the recurring (Unpivot) fields to line up, as below:

                   

                  Fixed1         Fixed2        Unpivot1   Unpivot2       Unpivot3        Unpivot4

                  Invoice#     Vendor       SKU#        Qty        Unit Price     Line Total

                   

                  0001           VenA             A011               2                1.00                  2.00 

                  0001           VenA             A012               1                3.00                  3.00 

                   

                  0002          VenB              A011               4                 1.00                  4.00 

                  0002          VenB               A013              5                 1.50                  7.50 

                   

                   

                  Many thanks for your help

                   

                  Al

                   

                  • Re: How to Model Flat Invoice Records in Excel File
                    Steve Caiels

                    Hi Al,

                     

                    My apologies, I think we missed a crucial step!


                    Starting from this:

                    Fixed1   Fixed2   Fixed3   Line1a   Line1b   Line1c   Line2a   Line2b   Line2c

                    abc      xyz      def      aaa      bbb      ccc      ddd      eee      fff

                    123      789      456      111      222      333  


                    First, merge all line item elements into a single field - One for each set of line item fields.  For example, Select Columns LineItem1a, LineItem1b and LineItem1c and 'Right Click->Merge Columns->Quick Merge'.  Select a split delimiter that you won’t get in your data and remove the original columns.

                    Repeat for the other groups of line items.

                    Now you should have this:

                     

                    Fixed1    Fixed2    Fixed3    MergeText1       MergeText2

                    abc       xyz       def       aaa^bbb^ccc      ddd^eee^fff

                    123       789       456       111^222^333      ^^

                     

                     

                    • Unpivot the newly merged fields by clicking the key on MergeText1 and 2, and select the fixed columns 1 to 3 with the tick.
                    • Remove the Attribute column
                    • Split the Value on your split character (^ for example)

                     

                    This results in:

                     

                    Fixed1  Fixed2  Fixed3  Value1  Value2  Value3

                    abc     xyz     def     aaa     bbb     ccc

                    abc     xyz     def     ddd     eee     fff

                    123     789     456     111     222     333

                    123     789     456                                        .

                     

                    Is that what you need?

                     

                    Regards,

                    Steve.

                    • Re: How to Model Flat Invoice Records in Excel File
                      Al Rice

                      Thanks Steve.  This is a brilliant recommendation. I will try it, and I am sure it will work perfectly.

                       

                      This is a great forum.  Always has been helpful for me.

                       

                      Al