6 Replies Latest reply: May 15, 2014 10:08 AM by Data Kruncher RSS

    Monarch Pro question

    chickenjoy _



      I am trying to export to tab delimited format. However, why is it that when a column has an 8-digit numeric in it...the columns become out of line? I cannot use delimiters or quotes in this file and the columns must line up correctly. Can you please advise?


      Attached is my sample exported text file.



        • Monarch Pro question
          Data Kruncher

          That's your text editor's (Notepad?) pre-programmed way of displaying files which include tab characters. It's programmed to have "tab stops" every x number of positions. Notice that 12009710 ends just before the regular tab stop shown in the other lines. Then it reads a tab character, which affects the position of the start of the next number (which of course will be the next tab stop). Tab stops look like they are set for every 9th position.


          With many text editors that allow you to modify the tab stops you'll have full control. Try one of those, like MS Word.

            • Monarch Pro question
              Olly Bond

              Hello Chickenjoy,


              You could export your file using another delimiter, and then use the Monarch Utilities to replace this with the tab character throughout the file. MSRP is what you need - if you contact Datawatch Support they should be able to give you a link to the download and advice on how to use it.


              Best wishes,



                • Monarch Pro question
                  chickenjoy _

                  Hello Guys,


                  I am searching for a filter formula that I can use. Lets say I have 100,000 rows in a table and I have a field named Order Number. The order number field property is numeric and the values for each row are not sequenced (e.g. row 1 = 1234, row 2 = 456 , row 3=090 .. and so on ..)


                  How can I display 900 records in the table randomly? I dont care whether they are not sequenced. I just want to display the 900 records on the table randomly. Even if they are jumbled then that's fine with me. Do you have a filter syntax to do that? It is similar to the .In.() function but you have to enter the values manually. In my case its 900 records I want tp show. thanks a lot for your help.




                    • Monarch Pro question
                      Data Kruncher

                      A bit of an interesting challenge that is.


                      First, on the far right of your Table window, create a Numeric calculated field named Random using the Rand() function.


                      Now add two more calculated Numeric fields, to be certain that you're getting the results that you want. The first is a Recno field, using the Recno() function, and then add a Rowno field using the Rowno() function.


                      Next, build a sort order based on the Random field, ascending.


                      Now we can see that the record numbers are nicely randomized, but we still have the entire data set, not just 900 records.


                      The only easy way to limit the number of rows is to use the Rowno() function in the filter, as in




                      but as I [URL="http://********************/filtering/overcoming-limitations-and-developing-top-n-tables"]coincidentally posted just yesterday[/URL], Monarch won't allow us to use Rowno() in a filter.


                      So the solution is of course the same as in that post, to export the table as is and then import that file as a database source.


                      You can then filter using:


                      Rowno < = 900[/CODE]


                      Note that this refers to your Rowno field, not the Rowno() function.


                      This is another practical example of overcoming the (few) limitations imposed by Monarch, and a particularly useful one for [URL="http://********************/tips/required-software-for-auditors"]auditors preparing samples for testing[/URL].


                      Of course the Recno field is superfluous now that we know the process works, so it could be removed from the table. Further, the Random field could (should?) be hidden from view.





                        • Monarch Pro question
                          chickenjoy _

                          Hello Kruncher,


                          Thank you very much for your suggestion. However, I would like to ask another question based on the suggestion you've mentioned.


                          Lets say I have a field called "order number" that has 5 -digits on each rows (e.g. 55606, 55102, 55456, 55089, 55033, 55230) and its 100,000 of them for example.


                          How can I display 900 records using specific values in a table? Not just through random. My question is similar to .In.() function in Monarch. So I would like to display 900 records of specific values I want based on the order number field. Please show me the steps again. Thank you!



                            • Monarch Pro question
                              Data Kruncher

                              With a specific list of relatively large number of "target" records - more than you'd want to maintain with an .In. operator - I'd be tempted to manage the list in an Excel file.


                              Export the whole list of order numbers to an Excel file. Open that file and manually remove the ones that you wouldn't want to list in an .In. expression, and then save and close the file.


                              Now in your Monarch model build an external lookup to the Excel file, importing OrderNumber as IncludeOrder. You'll have null values where the order number in Monarch doesn't exist in the Excel file.


                              Finally, build a filter using


                              OrderNumber = IncludeOrder[/code]and you'll have only the specific 900 records that you want.


                              The potentially difficult part is in refining the Excel list, but there had to be some "real" work somewhere.