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.
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.
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.
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:
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.
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!
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
The potentially difficult part is in refining the Excel list, but there had to be some "real" work somewhere.