11 Replies Latest reply: May 15, 2014 10:12 AM by Data Kruncher RSS

    Data manupilation Issue

    Mandvika _

      I created a template and exported data to a table this is how the table looks like-

      Line        ItemNumber       Quantity   PO Number           Order Date             CancelDate

      10     21337688     34     4200264793     05/11/2011     10/17/2011

      20     21337689     69     4200264793     05/11/2011     10/17/2011

      30     21337690     70     4200264793     05/11/2011     10/17/2011

      40     21337691     52     4200264793     05/11/2011     10/17/2011

      50     21337692     55     4200264793     05/11/2011     10/17/2011

      60     21337693     95     4200264793     05/11/2011     10/17/2011

      70     21337694     90     4200264793     05/11/2011     10/17/2011

      80     21337695     59     4200264793     05/11/2011     10/17/2011

      90     21337696     43     4200264793     05/11/2011     10/17/2011

      100     21337697     88     4200264793     05/11/2011     10/17/2011

       

       

      My problem is that I need to use this data and create an export in excel which looks like this -

      H5000101011820PONUmberDC4OrderDate~CancelDate

      L1011820PONUmberLineItemNUmberQuantityCancelDate

      L1011820PONUmberLineItemNUmberQuantityCancelDate

       

      where H,L, 1011820 etc are default values but the other values come from table..

       

      H stands for header which uses the PONUmber, OrderDate and cancel date only

      but the line items that follow that (L) use itemnumber, quantity etc...

       

      At this point I only have the table which is extracting the data....I have no clue hot to proceed to achieve this.  Also, I don't how how to set these default values lke H, L, 1011820 etc.

       

      PLease please guide me on how to go about doing this in MOnarch.

       

       

      I will be very thankful to you!

       

      Let me know if I am unclear of if you need nay other information.

        • Data manupilation Issue
          Grant Perkins

          Hi Mandvika.

           

          Looks to me like your extraction is only giving you order line data  - no header lines. However most of the variable data youe need to build a header line as in the order line, the rest being defaults (if I read your description correctly.)

           

          So this starts to look like a double export requirement.

           

          For the Order Lines you have all of the details (or so it seems) and just need to add two calculated fields for the first two columns of the output. If they are aleays the same values you can somply put the values into the fields and store them in the model. If they are variables you can prompt for the values from human input or, if you automate the process the values can be included in the automation script or program - we can come back to that later.

           

          For the header lines you will need a separate output - basically a summary per order with just the field you need - then export that and add some fields. Now that's not difficult but may involve a little more work since you likely have to start from a Summary and have no easy way to add calculated field values at that point. However there are plenty of ways to consider for adding the additional fixed fields for the order header lines. The somplest one might be to have another simple model that reads the header output summary back into a Monarch session, adds the calculated fields and then exports it again in the format that you require.

           

          A final step would be to combine both files  - order lines and header lines - and sort them into the order you need. From your example a sort by line type and order number would do that but we can make it a lot smarter if required. The combination could be in Monarch (followed by a final export) of possible using any other program that will read the file and has suitable sort options.

           

          So, thats an overview of one approach that should work. How comfortable are you with trying it in principle to see how far it gets you?

           

           

          Grant

            • Data manupilation Issue
              Mandvika _

              Looks like a good option. I can try it but I would need more guidance from you as to howI can move on how should I start. I wanted to bring to your attention that I plan to use the same model to extract data from muntiple pfs automatically and combine all the data in this format in one excel file.  Will it be possible to achieve this through your proposed solution?

               

              also what should I start with the orderline or headerline? Do I need another template to extract order and header lines separaretly or I can proceed with data in the table? Please guide me on where to start from.

                • Data manupilation Issue
                  Olly Bond

                  Hello both,

                   

                  I think you can get away with a single pass export using some hidden key fields in a summary.

                   

                  @Mandvika, if you email me the report and model I'll have a bash for you.

                   

                  Best wishes,

                   

                  Olly

                  • Data manupilation Issue
                    Grant Perkins

                    Looks like a good option. I can try it but I would need more guidance from you as to howI can move on how should I start. I wanted to bring to your attention that I plan to use the same model to extract data from muntiple pfs automatically and combine all the data in this format in one excel file.  Will it be possible to achieve this through your proposed solution?

                     

                    also what should I start with the orderline or headerline? Do I need another template to extract order and header lines separaretly or I can proceed with data in the table? Please guide me on where to start from.[/QUOTE]

                     

                    Before looking at this in more detail let's see where Olly goes with the single pass approach. If that works you won't need to go down this route.

                      • Data manupilation Issue
                        OddJob _

                        Hi Mandvika

                         

                        If I understand the problem correctly, the main difficulty is in creating a header record that doesn't currently exist in the table.

                        If the report that you are extracting data from is straightforward, one approach might be to amend the detail template to include the header line i.e. the line that contains the field names above the first detail line in the report.

                        Assuming that the field types are all set to numeric or date, you should then see an additional record at the top of your table that contains all Null / Blank fields.

                         

                        If you then create an Append template to extract just the first detail record i.e. trapping on the Line No 10, your table will hopefully contain a record for each detail line and an extra record which has the necessary PO Number, Cancellation Date etc tagged onto the end of it, pulled from the first detail record.

                         

                        Then comes the hard work; creating some calculated fields to pull out the relevant bits of data from each record, depending whether it's a header record or a detail record.

                        e.g. If(IsNull(Line),"H","L") etc.

                         

                        You can pull out all of the individual bits of data using calculated fields and then concatenate them. Obviously, you might also have to use Trim() to take out extra spaces and possibly convert numbers and dates to text to produce the text string.

                         

                        If there is just one order per file then, when you open multiple files, each file should create a header record and detail records.

                         

                        Hope this helps

                         

                        Edit: Having looked at this again, the Append template should probably be a Footer template. Also try setting the 'cleared by' option to  'Detail'.

                        I would also suggest setting all field types to Character in both templates.

                        I have only tried this in V11 so the behaviour in earlier versions might be different.

                         

                        Edit: I've now tested this on V9 Pro. It appears to work OK. The only thing missing is the facility for the Footer Template to be cleared when it hits the next Detail Template.

                        I can't remember whether this was in V10 or whether it's new in V11.

                        All this means is that you get the additional fields appended to the first detail record as well as the dummy header record which shouldn't cause a problem.

                          • Data manupilation Issue
                            Mandvika _

                            Whenever I export the summary table to a txt or csv file I get extra spaces in the end of each record. I tried using the trim() function on the two fields I am using to create the summary  but that did not work. Please help me with how I can get rid of these spaces in the data.

                             

                            Olly,

                               Thanks a lot for all the help in creating the model. I sent you the sample files with the latest version of the model and sample csv file. The csv file is missing the header line data and there are extra spaces in the header line row and after each line item row. Please help me resolve this problem.

                              • Data manupilation Issue
                                Olly Bond

                                Hello Mandvika,

                                 

                                The issue is that your H records are maximum 87 characters wide, and the L records 90 characters wide. You might be able to export to text, open the text file in Monarch, trap every line, make a calculated field using RTrim(), then hide the original field and export the table to Excel.

                                 

                                What happens when you export the summary directly to Excel?

                                 

                                Best wishes,

                                 

                                Olly

                                  • Data manupilation Issue
                                    Mandvika _

                                    When I export to excel all the hidden fields also show up in excel...but I am more concerned about the output in csv format

                                     

                                    When i export table to csv with fixed-length option selected the export comes fine.

                                    What I need is that I should be able to export the Output summary table to csv without the spaces. The max length of line item details may change depending on the input file that is why I cannot restrict the length of H and L fields.

                                    Is opening the file as txt and then manulipalating the txt file the only way to get rid of extra spaces?

                                     

                                    Is it possible to change something in the model to fix this extra spaces problem because this model will be used by end users and it wont be a good idea to have them export data to txt and then open the txt and resave it to csv in order to get rid of paces.

                                      • Data manupilation Issue
                                        Olly Bond

                                        Hello Mandvika,

                                         

                                        Ah, end users - bless 'em Why not automate a two stage process - on the desktop with Data Kruncher's Batch File Generator, or for unattended operation, with Monarch DataPump?

                                         

                                        Best wishes,

                                         

                                        Olly

                                        • Data manupilation Issue
                                          Data Kruncher

                                          Without looking at the sample documents and model(s) that you and Olly have been looking at, and only focusing on the sample in the original post, I think that I may have discovered the root cause of the problem here: it's that you're exporting m/d/yyyy formatted dates.

                                           

                                          If the system that's importing your data can handle mm/dd/yyyy formatted dates then you should be off to the races.

                                           

                                          It's the difference in the varying field widths of the dates that's causing Monarch to pad the remainder of the line with spaces. If you force the dates to always display as mm/dd/yyyy then Monarch will not add padding spaces at the end of selected lines.

                                           

                                          Unfortunately Monarch does not offer a mm/dd/yyyy Date type field format, so we must use a Character type calculated field to get what we want.

                                           

                                          For instance, if your date field captured from the report is named PreShipDate, then this formula will generate an mm/dd/yyyy Character "date" field:

                                          Trim(Str(Month();2;0;"0"))"/"

                                          Trim(Str(Day();2;0;"0"))"/"

                                          Trim(Str(Year()))[/CODE]The critical step in defining this calculated field isn't only this formula: you [I]must /I[/B]set the field to be 10 characters wide, otherwise the summary will include (and export) extra unwanted spaces.

                                           

                                          Once you have as many new calculated date fields as you need, in your summary remove the existing date fields and add the new calculated date fields as necessary.

                                           

                                          Export the summary and you should find, as I did in my testing, that your fixed-length file with a csv extension has no trailing spaces on any line.

                                           

                                          HTH,

                                          Kruncher