10 Replies Latest reply: May 15, 2014 9:55 AM by ndello _ RSS

    Here one!

    ndello _

      Good morning all,

       

      I open 2 reports at the same time and apply my model.

      I only have 3 fields, Trade ID, Cust ID, Book, but about 400 rows of data

      It would look something like this

      65652US, BTMYNY, CMGNFS02

      65652US, ,

      12345US, CITIUSNY, MTM2

      12345US, ,

      Basically, you would see the same Trade ID twice, but the other 2 fields only once associated with that Trade ID

      I want to create a summary, but only showing all three fields when the trade ID is not duped.

      So if 65652 Trade ID above is only seen once, then I want that whole first top row to appear.

       

      Can this be done?

      Hope this makes sense.

      Thanks

      Neil

        • Here one!
          Dee Moore

          Hi Neil,

           

          You didn't mention which version of Monarch you work with.

           

          For V7 & V8:

           

          In the Table Window, either create a new Filter or Edit a Filter you need to use in the Summary. Go to the Advanced Tab and choose "Show only rows that are unique with respect to the selected keys." Place a check mark in the TradeID box.

           

          Make sure you select this Filter in your Summary Definition.

           

          Regards,

          Dee Moore

          Datawatch Tech Support

          • Here one!
            ndello _

            Thanks Dee, I am using version 8.

            Here is my problem,  I only want to see what isn't duped. How can I do that?

            Thanks again

            • Here one!
              Dee Moore

              Hi Neil,

               

              Ahhhh. Maybe I didn't understand exactly what you wanted to do.

               

              If I understand correctly now, you do not want to see any data rows that duplicate. Not even one instance if the TradeID has a duplicate.

               

              This would be a three step process, but can still be accomplished in the same Model:

               

              First, create an Advanced Filter using "Select all rows for which there are duplicate values for the specified fields." for the TradeID (or any field you choose for your Key).

              This will return all the duplicating data, none of the unique data.

               

              Second, create a Lookup Calculated Field (character) using the TradeID/Key as the Input Field.

              Make sure the new filter is active (very important).

              Get the Input Field Values and quickly type "d" (for duplicate) down the Output Value column.

               

              Leave the other settings as they are. This will produce a "d" in all the duplicating data rows and "null" in the unique data rows. In the General Tab, Hide the new Lookup Field.

               

              Now create a new Filter:

              <> "d"

               

              You should only see unique data rows.

               

              I think this is what your looking for?

               

              Regards,

              Dee Moore

              Datawatch Tech Support

              • Here one!
                ndello _

                Thanks Dee, but now everytime I have new data (trade ID changes everyday), do I have to update the Output Value with a D (there are always more than 200).

                Thanks

                • Here one!
                  Dee Moore

                  Hi Neil,

                   

                  Well, until we can get an Option in the Advanced Filters to exclude all duplicates, you have three choices.

                   

                  1. Process the files as I had suggested. Unfortunately it would be necessary to Edit the Lookup field and manually enter the character.

                   

                  2. Export the unfiltered data to a database format (like Access) and handle the duplicates at the database level.

                   

                  3. This is a bit cumbersome but, once set up, you may like it....

                   

                  A Summary Tab may be created by bringing in only the TradeID and the Count.

                   

                  In the Properties of the TradeID, set the Matching Tab to return the Measure (Count) at most equal to 1. This will identify all unique TradeID's.

                   

                  Export the Summary creating a new .mdb file.

                   

                  Go back to the Table Window and create an External Lookup joining to the .mdb file on the TradeID and bring in the Count field. You will now have a Temp* field that contains the value of 1 representing unique TradeID's and (null) representing duplicates.

                   

                  Finally, build a Filter

                   

                  Temp* = 1

                   

                  For each new report you will need to Export from the Summary Overwriting the .mdb file, and Refresh the External Lookup.

                   

                  I found creating a Project Export for the Summary and saving all as a Project worked nicely.

                   

                  Once it is set up properly you just need to Open the Project, select the new Report, retaining the Model. Run the Project Export and Refresh the External Lookup. And, it's easy to check the records being returned in the Table against the records returned in the Summary for accuracy.

                   

                  Let me know if you like this better.

                   

                  Regards,

                  Dee Moore

                  • Here one!
                    ndello _

                    Dee, thank you very much for your time. What I do now is export to Excel (my Excel skills are pretty good) then handle it all there.  However, I was hoping to handle it all within Monarch.  Basically making it a one touch operation.

                     

                    Hopefully in the next version, we can filter by non-dupes.

                    Thanks again for your efforts and time.

                    Regards

                    Neil

                    • Here one!
                      Grant Perkins

                      Neil,

                       

                      from what you wrote it sounds like the duplicate trade ID rows will always have blank second and third fields if there IS a second row. Is that correct?

                       

                      If so a quick and dirty approach, which works with the sample lines posted, would be to think of the record as a possible multi-line, single field record.

                       

                      Trap for the first (will always exist) 3 field row by using, for example, both the first field and the second field requiring some characters (whereas the duplicate row would not have characters in the second and third field positions ...) and then set the properties of the field to End on "None of the Above".

                       

                      If you can achieve the extract consistently using that approach then you might get records that look like this:

                       

                      65652US, BTMYNY, CMGNFS02 65652US, ,

                      12345US, CITIUSNY, MTM2 12345US, ,

                       

                       

                      If you make a filter based on a formula something like this

                       

                      [font="courier"]lsplit(,2," ",1)<>rsplit(rtrim(),3," ",2)[/font][/quote]It will filter out any records where the first and last populated 'fields' are the same.

                       

                      (There are a number of ways of doing something along those lines. Another approach might be to seek another occurence of the TradeID code at a later point in the field, for example.)

                       

                      You would be left with only the non-duplicate lines showing in the table as a single field. To split the data from the single field into the 3 separate fields create 3 calculated fields using a formula based on LSPLIT and perhaps using the space or "," as the separator. "," would be better since the TRIM based functions can be used easily to dispose of leading or trailing spaces.

                       

                      You can use the same formula for each field and just change the section of the field that you need to be returned in that column of the table.

                       

                      Is this suggestion any use or your full report and process?

                       

                       

                      Grant

                      • Here one!
                        ndello _

                        Thanks Grant, I will give it a try.

                        What happens is I open 2 reports and use the same model because one report has many columns of data, of which three I use.  The second report only has the Trade ID and trade id is in the exact same position for both reports.

                        I can create a template that will key off of only the report that has more data (this report has much more data than only the three fields I use).

                        Questions:

                        1.  Do I do None of the above for all three fields?

                        2.  My second report with just the trade id has nothing highlighted, so I am not sure how to key on that?

                        Thanks for helping

                        Neil

                        • Here one!
                          Grant Perkins

                          Hi Neil,

                           

                          If you open the two reports as you describe the reports lines for a single Trade ID will not be consecutive, as they are in the sample, so the idea would not work. However if you opened both reports trapping every data line and then sorted the resulting table you could get the table in the format you posted.

                           

                          Save that as an exported file and then re-open it with a model as I described and it should work. (To answer the second question first!)

                           

                          The answer to the first question is that you would not define the 3 fields separately to start with - you would create a siongle field for the whole of each recod and then split it once the duplicates has been removed.

                           

                          HOWEVER. What would make more sense on the basis of this description would be something more akin to Dee's suggestion. I would use the second report - TRADE ID ONLY - to create a simple external lookup table with 2 columns - TRADE ID and a flag  - Dee's "D" for example.

                           

                          Then load the 3 field report using a model that also includes an external lookup to the lookup table just created. and populate the three field table with a fourth field for the "D" flag.

                           

                          Now filter on the D.

                           

                          That's it.

                           

                          Quite easy to put this into a batch file to simplify the process. In fact there may be a few interesting way s that the entire process could be set up.

                           

                          There is also a possibility to do this by loading both reports and dealing with it in a SUMMARY accoriding to how many times the TRADE ID is COUNTED. You would display details where the count did not exceed 1 for example. However you would also see the the total count for 'everything else' at the bottom of the report and this may be undesirable for your purpose, though you could always edit out those lines before distributing the final document.

                           

                          Nevertheless it might be worth investigating.

                           

                          HTH.

                           

                           

                          Grant

                          • Here one!
                            ndello _

                            Thanks Grant, I changed it a little.

                            I used your suggestion and exported, then opened.  I then created the new template as you described.

                            In table view, I then added a new formula field with this formula:

                            if(right(,2)="US","Dupe","No Dupe")

                            I then create a summary of No Dupes and it gives what I want.

                            Thanks again