How to Model Flat Invoice Records in Excel File
Altair EmployeeHello 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
Answers
-
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.
0 -
Altair Forum User said:
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.
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
0 -
Altair Forum User said:
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
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.
0 -
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.
0 -
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
0 -
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.
0 -
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
0
