9 Replies Latest reply: May 15, 2014 9:57 AM by Greg Walsh RSS


    Greg Walsh

      I'm a seldom user and have a question.  I have a report where the ID is only listed once, but I have two other fields.  One may have three entries while the other has two. On the next record it could be one entry on the 2nd field and five on the 3rd field. I'm not sure how to handle this, any ideas, thanks.

        • Template
          Data Kruncher

          Hi Greg,


          It would be very helpful if you would please post a sample of your report if at all possible.


          Use the "Code" button, and paste your report between the tags. You can edit out any confidential data with x's and 9's if you wish.




          • Template
            Greg Walsh

            /size[quote]code:[/size]NUMBER     DESCRIPTION         CODE     NEEDED        ON HAND   QUANTITY   PALLETS  NUM    ON HAND     SHORT       ON ORDER DATE                                                                               

            01451     DC100 12:12X10:06X1: N00NN     2,816           993       1,823             986    2,750                                                                               

            01481     RSC19.94X14.25X4     N00NN       721             0         721                                 721          3,000  4-26- 7                                                                               

            02644     8 3/4 X 6 NESCEL     N00NN    52,560        18,552      34,008      .99    074   97,200                    66,150  5-10- 7                                                                               

            03183     B/GE 4 1/2X2 7/8X1 1 N00NN     3,240           964       2,276             074   10,237                                                                               

            03567     B/STA 7:10X4:10X1:8  N00NN    10,765        10,603         162                                 162         11,362  5-14- 7                                                                               

            897  6- 4- 7                                                                               

            03568     L/BBS 7:12X4:12X1:8  N00NN     7,280         7,071         209                                 209          8,618  5-18- 7                                                                               

            03616     B/STA 7 5/8X4 5/8X1  N00NN    20,000           233      19,767                              19,767         16,290  4-27- 7                                                                               

            3,620  5- 1- 7                                                                               

            03881     L/BSS 7:12X4:12X1:03 N00NN    20,000           391      19,609                              19,609         13,098  4-27- 7                                                                               

            6,642  5- 1- 7                                                                               

            04910     BURG/BRNZ 611 COVER  N00NN    68,936        61,037       7,899             009    4,048                    33,873  5- 4- 7                                                                               

            025    1,754                    15,120  5-18- 7                                                                               

            035    4,527                    11,340  6-15- 7                                                                               

            51,912  6-15- 7                                                                               

            04911     BURG/BRNZ 611X113 CA N00NN    59,256        45,698      13,558             009    1,848                    44,980  5- 4- 7                                                                               

            025    1,508                    11,340  6-15- 7                                                                               

            074   11,340                    39,856  6-15- 7                                                                               


            • Template
              Grant Perkins



              I see you have V6.   Pro or Standard?




              • Template
                Greg Walsh


                • Template
                  Grant Perkins

                  Ah! An extra challenge ...


                  I think it is now time to ask what you desired output needs to be.


                  Which columns do you need, what is the lowest level detail required?




                  • Template
                    Greg Walsh

                    That's my problem, I need all the data shown.  Do I need a different version to achieve this?

                    • Template
                      Grant Perkins

                      Originally posted by Greg Walsh:

                      That's my problem, I need all the data shown.  Do I need a different version to achieve this? /b[/quote]Greg,


                      A version at PRO level would likely make life easier.


                      However there is a difference between needing all the data (perfectly reasonable though I suspect that one or two columns may not be important according to your sample - they might be in other samples of course) and what the needs are for the display of the data.


                      For example do you need the "Num" and "On Hand" fields as real data or as useful Memorandum fields for information but not analysis? They have the look of being information fields which are advisory rather than for analysis. In which case they could be captured in multi-line character field as a 'Memo'.


                      On the other hand since some products don't have "On Order" data  maybe they need to be taken as individual data fields.


                      The bottom line is that you need to be clear about what the DETAIL levle for the data might be - the lowest level of analysis where the data component needs to be in its own field. If the answer to that is either or both of the column sets I have mentioned above then, yes, life will be interesting. Mainly because you effectively have 2 or maybe three reports in one output.


                      The classic suggestion here wouold be to extract the On Order info to a lookup table using a suitable 'key' to the product. In a separate process do the same with the On Hand data then recombine them into a common file. But if you do that using a lookup table in Standard you would need to manually populate the internal lookups (via cut and paste) each time you run the process. The Pro version would allow you to simply create external lookup files and then use them in the third model process.


                      That said you may still end up with a fudged result so I am wondering if there are alternatives.


                      So - the key is what the extracrted table need to look like. If it needs a populated line for every line that has either or both of an "On Hand" or an "On order" entry  - and assuming it is the sort of report where one or the other of those will always be populated - it may be best to seek out an unusual approach to the problem.


                      Does your table need to have every field represented (even if empty) for all fields of every line of the report (expect blank lines?). WOuld that allow you to do the analysis you require?


                      If so I have some ideas to experiment with.



                      • Template
                        Grant Perkins



                        Here is a suggestion to try.


                        For your detail template trap select a full data line as the sample. Use the NUMERIC OR trap ( "|" ) on the last digit of both the SECOND On Hand column and the On Order.


                        That will make every line a detail line unless blank or a header.


                        Paint the field you require. Include everything from NUM to the right for sure. (For the other fields it will depend whether you want them to appear only once, as in the first line of an account group, or for every line by making them  part of an append template. Either, or indeed both approaches, are possible. Some will certainly need to be in an Append template.)


                        The date in the last column may need a little work but we can come back to that.


                        Finish that template and add another as an APPEND. Trap for a NUMBER at the first position of the line. Paint the remaining fields you require. Save the template.


                        Review the table.


                        You should have everything you need though the date will likely still be a problem at this point.


                        Are you OK with deciding how to fix the date or would you like some suggestions for processing it into a recognised format? Its current format is pretty odd but there are a number of ways to fix it.



                        Does this help?





                        Edit to add: In fact the date was less of a problem than it appeared to be. Despite the unusual format Monarch did recognise the data as a date but the initial display I saw from the  cut and paste text file created  from the posted example truncated the output by 2 characters.


                        [size="1"][ April 27, 2007, 10:01 PM: Message edited by: Grant Perkins ][/size]

                        • Template
                          Greg Walsh

                          Grant, thanks for the help, this last suggestion, with some trial and error, did the job.  I really appreciate the help.