ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Unique Records

Post new topic  Reply to topic Goto page 1, 2  Next
 Unique Records « View previous topic :: View next topic » 
Author Message
arunkumo
PostPosted: Tue Jan 21, 2020 5:37 pm    Post subject: Unique Records Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

Hi,

I want to calculate unique records while I received from File . The file having number of records and repetitive records like below.

Header file 21012010
Bose,sbi,21022010
sati,icic,21012020
sam,axis,21012010
pandey,ocb,20012020
Bose,sbi,21022010
sati,icic,21012020
Trailer file 6

If I use cardinality , it is displaying count as 6,but I want to display count as 4. No need to bother about header and trailer.
I want to avoid duplicate records. Is there any way in Esql ?
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Wed Jan 22, 2020 2:01 am    Post subject: Reply with quote

Knight

Joined: 17 Jul 2017
Posts: 516
Location: UK

hi...One way to do it is to iterate the records and start adding it to a new array but only after checking whether the element already exists in the new array...by the end of the processing the new array will only have unique records.

Another way, in case any of the three columns are supposed to be unique ones (eg: the name) then you could use the approach suggested by mqjeff at the below thread,

http://www.mqseries.net/phpBB2/viewtopic.php?p=404056&sid=a8c6da254bff879e37f49a32e8681d5e

Quote:
Or you could create a tree in the XMLNSC domain in Environment or Local Environment, and use the x/xtypes/xtype/xtypeName as the top level element of the tree... something like

set NEXT CHILD OF OutputLocalEnvironment.XMLNSC.DistinctNames.{InputRoot.XMLNSC.x.xtypes.xtype.xtypeName} = curRef

for some reference curRef looping over the input tree.
Back to top
View user's profile Send private message
arunkumo
PostPosted: Wed Jan 22, 2020 1:50 pm    Post subject: Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

Thank you Abhi. I have tryed this option but it didn't worked out.
I can do iteration but ,we r getting 1000 of records, automatically processing impact slow performance.

I have referred below link, but it doesn't worked out.
http://www.mqseries.net/phpBB2/viewtopic.php?t=51305&start=0&postdays=0&postorder=asc&highlight=
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Thu Jan 23, 2020 1:39 am    Post subject: Reply with quote

Knight

Joined: 17 Jul 2017
Posts: 516
Location: UK

arunkumo wrote:
Thank you Abhi. I have tryed this option but it didn't worked out.


hi...can you please be more specific, did it work for few records or didn't work at all?

If you are dealing with 1000s of records in the same file with numerous duplicate entries I would try getting the source application to deal with the duplication. What is the guarantee that they don't send duplicate entries across two different files?
Back to top
View user's profile Send private message
nick12
PostPosted: Fri Jan 24, 2020 6:43 pm    Post subject: Reply with quote

Novice

Joined: 14 Jan 2020
Posts: 14

I would recommend trying to use a java or .net compute node and using a data structure purpose-built for this kind of thing.
Back to top
View user's profile Send private message
arunkumo
PostPosted: Sat Jan 25, 2020 8:38 am    Post subject: Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

Thank you Abhi and Niki.
It doesn't worked with ESQL-COmpute Node. Well I have achieved this by using xsl transformation node as of now. Because if i want to use java,it suppose to compare and 2 looping techniques to be used.If it is 10,000 records, i assumed processing might be slower.So i went with XSL node and just 3 lines of code, it sorted the duplicate records.
I have written the xsl code in .xsl file. I have given the location of .xsl file in xsl node, before that i set the encoding and char set id in compute node.
Back to top
View user's profile Send private message
timber
PostPosted: Mon Jan 27, 2020 8:24 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Quote:
It doesn't worked with ESQL-COmpute Node
The technique suggested by abhi_thri/mqjeff does work. I think you mean that you could not work out how to do it
Quote:
if i want to use java,it suppose to compare and 2 looping techniques to be used.If it is 10,000 records, i assumed processing might be slower.
Who told you to use 2 looping techniques? Or any looping techniques at all? A HashMap or Dictionary would do the job quickly and simply without any looping at all.
Quote:
i went with XSL node and just 3 lines of code, it sorted the duplicate records
Glad you got it working. Just be aware that you are parsing your input twice. The XSL engine parses it once, then IIB's XMLNSC parser parses it a second time. That it probably not optimal.
Back to top
View user's profile Send private message
arunkumo
PostPosted: Mon Jan 27, 2020 11:52 am    Post subject: Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

[quote="timber"]
Quote:
It doesn't worked with ESQL-COmpute Node
The technique suggested by abhi_thri/mqjeff does work. I think you mean that you could not work out how to do it

Can you help me ,how to achieve in esql. If that works, I will remove my xsl node.
Back to top
View user's profile Send private message
arunkumo
PostPosted: Mon Jan 27, 2020 8:21 pm    Post subject: Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

DECLARE tempGroup,temprow ROW;
DECLARE index INTEGER 1;
SET tempGroup.EmployeeInfo[1] = InputRoot.XMLNSC.EmployeelList.EmployeeInfo[1];

FOR loopField AS InputRoot.XMLNSC.EmployeelList.EmployeeInfo[] DO
IF NOT EXISTS (SELECT Field FROM temprow.EmployeeInfo[] WHERE tempGroup.EmployeeInfo = loopField) THEN
SET tempGroup.EmployeeInfo[index] =loopField ;
SET index=index+1;
END IF ;
END FOR ;

This code is giving the same count of cardinality. I.e without removing the duplicates.
Back to top
View user's profile Send private message
timber
PostPosted: Tue Jan 28, 2020 1:43 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Nobody has advised you to use that code - it is the wrong solution.

abhi_thri has advised you to look at mqjeff's technique here: http://www.mqseries.net/phpBB2/viewtopic.php?p=404056&sid=a8c6da254bff879e37f49a32e8681d5e

Have you looked at that thread yet? If so, what did you think?
Back to top
View user's profile Send private message
rekarm01
PostPosted: Tue Jan 28, 2020 2:38 am    Post subject: Re: Unique Records Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

Please use [code] tags when posting code, to preserve spacing; this makes well-formatted code easier to read.

arunkumo wrote:
Code:
IF NOT EXISTS (SELECT Field FROM temprow.EmployeeInfo[] WHERE tempGroup.EmployeeInfo = loopField) THEN ...

One problem here is confusing "temprow" with "tempGroup". Another problem is mixing up where the distinct key value is; is it in "EmployeeInfo", or "EmployeeInfo"."Field"? A third problem might be the spelling of "EmployeelList".

A SELECT function call in a FOR loop may not perform well for a large number of records. A simpler loop might exploit ESQL field references to copy element values to element names, and then rely on the SET statement to eliminate any duplicates, by overwriting existing elements, rather than creating new ones:

Code:
DECLARE tempRow ROW;
DECLARE recCount INTEGER;

FOR elemRef AS InputRoot.XMLNSC.EmployeeList.EmployeeInfo[] DO
    SET tempRow.{elemRef} TYPE = Name;
END FOR;

SET recCount = CARDINALITY(tempRow.*[]);

This example is not necessarily tested, optimal, or even useful, as-is, but may serve as a suitable starting point, for counting unique records.
Back to top
View user's profile Send private message
arunkumo
PostPosted: Wed Jan 29, 2020 7:43 am    Post subject: Re: Unique Records Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

Code:
DECLARE tempRow ROW;
DECLARE recCount INTEGER;

FOR elemRef AS InputRoot.XMLNSC.EmployeeList.EmployeeInfo[] DO
    SET tempRow.{elemRef} TYPE = Name;
END FOR;

SET recCount = CARDINALITY(tempRow.*[]);


I'm sorry, I didn't catch you. What is Name here and Type ?
Back to top
View user's profile Send private message
gbaddeley
PostPosted: Wed Jan 29, 2020 3:56 pm    Post subject: Reply with quote

Jedi

Joined: 25 Mar 2003
Posts: 2492
Location: Melbourne, Australia

How about sorting. The dups will then be together and can be skipped in a single iteration.
_________________
Glenn
Back to top
View user's profile Send private message
rekarm01
PostPosted: Wed Jan 29, 2020 6:41 pm    Post subject: Re: Unique Records Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

arunkumo wrote:
I'm sorry, I didn't catch you. What is Name here and Type ?

The Knowledge Center describes Name and TYPE in more detail.

In the given example, the elements in tempRow need unique names, but they don't need values, because CARDINALITY() doesn't count values; any of these other SET statements below should work equally well:

Code:
FOR elemRef AS InputRoot.XMLNSC.EmployeeList.EmployeeInfo[] DO
    -- SET tempRow.{elemRef} TYPE = Name;
    -- SET tempRow.{elemRef} VALUE = NULL;
    SET tempRow.{elemRef} = '42';
END FOR;

Again, this is only an (untested) example; feel free to modify it, as needed.
Back to top
View user's profile Send private message
arunkumo
PostPosted: Fri Jan 31, 2020 4:44 pm    Post subject: Re: Unique Records Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

Again, this is only an (untested) example; feel free to modify it, as needed.[/quote]

Thank you.. it doesn't worked out.. and thank you so much for your valuable thought.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Unique Records
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.