Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Datawindow, search for dupes in symbol relations

  1. How-to
0
Votes
Undo
  1. Michał Misijuk
    Michał Misijuk
  2. PowerBuilder
  3. Tuesday, 19 April 2022 09:32 AM UTC

Hello :)
I have a table, which contains relations between, let's call them "symbols". Looks like this:


I want to prevent user from saving data when inputs 2 records like marked on the screen (symbol => symbol_to, and in another symbol_to => symbol). We ignoring type column, i want to search only symbol/symbol_to. 
My only idea is to loop over every record, and for every record loop over all records and check for column values, BUT if this table grows too much, that could be not efficient - f.e. for 500 records is 500x500... 
Mayby someone has another idea?
Thanks!

EDIT: another idea i came up with - get data from DW, put into temporary table in oracle, run select which is simple, and retrieve it. But mayby is a way to do this in PB?

  1. DataWindow
  2. PowerBuilder
SLE in RibbonBar
Rich Text Edit Control, TX Text Control ActiveX 28
Who is viewing this page
Jürgen Hock
Amit Kumar
Accepted Answer
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 19 April 2022 11:59 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi,

 

You can loop through all rows and for each row use Find function to try to find a "duplicate" row. I think you need to begin search only after the row you are running on. This should be faster then looping over all records for each record.

e.g.

FOR ll_row = 1 TO ll_rows - 1
  ll_find = dw_1.Find ("symbol='" + dw_1.GetItemString (ll_row, "symbol_to") + "' AND symbol_to = ' + dw_1.GetItemString (ll_row, "symbol") + '", ll_row + 1, ll_rows)
  IF ll_find > 0 THEN
     // duplicate found
  END IF
NEXT

 

If you only need to know whether there are such duplicates you can do it with a computed field (or evaluate expression) using distinct count. This only works well if you put the symbols in a fixed order. The expression could be

count (symbol for all distinct if (symbol < symbol_to, symbol + "$$" + symbol_to, symbol_to + "$$" + symbol))

If the count is different from rowcount you have duplicates (but you don't know what).

 

You can use this also with dw grouping functionality. You could order and group (e.g. in a second dw) by 

if (symbol < symbol_to, symbol + "$$" + symbol_to, symbol_to + "$$" + symbol)

and find the groups that have more then one row. In this case you also know the duplicates.

This should also work without grouping. Sort by the same expression and then find by

 if (symbol < symbol_to, symbol + "$$" + symbol_to, symbol_to + "$$" + symbol) =  if (symbol[-1] < symbol_to[-1], symbol[-1] + "$$" + symbol_to[-1], symbol_to[-1] + "$$" + symbol[-1])

 

HTH,

René

 

Comment
Michał Misijuk
  1. Michał Misijuk
  2. Tuesday, 19 April 2022 12:19 PM UTC
Hi René. Thank You very much, i'd like to ask a few questions:

1.count (symbol for all distinct if (symbol < symbol_to, symbol + "$$" + symbol_to, symbol_to + "$$" + symbol)) - this should be put directly in the DW?

2. dw grouping functionality - I'm not failmiar with such functionality. Is it also in the DW ?
  1. Helpful
René Ullrich
  1. René Ullrich
  2. Tuesday, 19 April 2022 12:54 PM UTC
The easiest way is to do the count using a computed field in the datawindow. You can get the value with GetItemNumber. If you don't want to change the datawindow you can also use the describe function of the datawindow control or datastore and use the evaluate function to dynamically evaluate the expression.

Grouping is a datawindow functionality. It is a special datawindow style but you can also create groups for tabular or grid datawindow (menu Rows->Create group) You can group by columns, computed fields or expression. All rows that match the group specification are in one group. The next not matching row starts a new group. Because of this it is importent that you correctly set the datawindow sort! With grouping you can use aggregate functions like count only for groups, e.g count (mycol for group 1) counts the number of rows in each group of level 1 (you can have different group levels - level 2 is a sublevel of level 1).
  1. Helpful
Michał Misijuk
  1. Michał Misijuk
  2. Wednesday, 20 April 2022 07:12 AM UTC
"Find" did the work! After finding duplicated records I wanted to get symbols which has dupes, search through table and point exact records so that's was all i need. Thanks!
  1. Helpful
There are no comments made yet.
Responses (4)
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Tuesday, 19 April 2022 13:04 PM UTC
  2. PowerBuilder
  3. # 1

Let's say we have a datawindow with the following columns:
- symbol_from
- symbol_to
- compute4check
- issue_flag (with default value 0)

I do add a computed field (compute4check) with the folowing expression:

if( symbol_from > symbol_to , symbol_to + '-' +symbol_from , symbol_from + '-' +symbol_to )

This will create a concatenation of symbol_from and symbol_to always placing the "smaller" one to the beginning.
So ABC -> DEF and DEF -> ABC will both become "ABC-DEF". This run on the fly.

When I have all my records, all I have to do is to sort my data based on computed field.
After that I can loop to all the rows. If I find any row where computed field for [row] = computed field for [row + 1], I know it is wrong. Whenever I find a row where this condition is valid I do put value 1 to issue_flag.

All rows with issue_flag = 1 are to be reviewed.

Andreas.

Comment
René Ullrich
  1. René Ullrich
  2. Tuesday, 19 April 2022 14:18 PM UTC
Yes, this should work.

PowerBuilder can also do a find (find is much more faster) to check for difference of one row with the previous row. But I think this only works for columns not for computed fields. See the last example in my post to see how to do this.
  1. Helpful
Andreas Mykonios
  1. Andreas Mykonios
  2. Tuesday, 19 April 2022 14:57 PM UTC
I know René. I already read your answer. :-)
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 19 April 2022 12:23 PM UTC
  2. PowerBuilder
  3. # 2

Hi Michal;

   FWIW: personally,  I've implemented this many times using the Filter() & RowCount() commands on a DataWindow or DataStore. Super fast & easy. Food for thought.  ;-)

Regards ... Chris

Comment
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Tuesday, 19 April 2022 09:55 AM UTC
  2. PowerBuilder
  3. # 3

Hi.

What is the list of allowed symbols? It's single letters or it could be something more complex?

Andreas.

Comment
Load more comments
Michał Misijuk
  1. Michał Misijuk
  2. Tuesday, 19 April 2022 10:47 AM UTC
A-Z 0-9



Yes, ABC-FED and BCA-DEF is allowed, diffrent symbols
  1. Helpful
Andreas Mykonios
  1. Andreas Mykonios
  2. Tuesday, 19 April 2022 11:00 AM UTC
One last question. Can you explain what does column type? Or its presence is independent for this check...

Andreas.
  1. Helpful
Michał Misijuk
  1. Michał Misijuk
  2. Tuesday, 19 April 2022 11:31 AM UTC
this is the type of the connection, it is irrelevant, 1 means " if symbol a exists then symbol b must exists also", 2 - "if symbol a exists then symbol b must not exists" so

a 1 b

a 2 b

it is a configuration error, so we skip it
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.