Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] MATCH Command SORT BY ISSUE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] MATCH Command SORT BY ISSUE
 Login/Join
 
Robing9
Member
posted
Hi all

I am trying to MERGE two data Sources using the "MATCH" Command it seems not working. Need help big time!!!!!!!!!!!!!!!!!
The issue is the Field by which i am MERGING "STK_NBE" column in one source is of "A18" format and the other is "A8".

STK_NEB = '100011' --- Sample data Source with "A8" format
STK_NEB = '000000000000100011' ----- Same data in the "A18" fromat.

For MATCH Both STK_NEB Should be in the same format. So i did a
"SET ASNAMES =ON
DEFINE FILE STK
LDZ/D8.2S=EDIT(STK_NEB);
AG/A8=FTOA(LDZ, '(D8c)', AG);"

To convert STK_NEB column in "A18" Fromat to "A8". But the solution seems not working. The coverted STK_NEB field data (AG calculated field)is printing properly (as 100011 for sample Data) but not working with MATCH. The result is shown below.

PAGE 1

STK_NEB Descr. DESC_TEXT
100011 CRANBE *****
100011 ***** CRANE
100011 ***** CRANE

This message has been edited. Last edited by: Robing9,


7.6, Unix, HTML
 
Posts: 18 | Location: Kochi, Kerala, India | Registered: August 26, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
Can you post your code between the code tags (red carats on the right)? What you are doing sounds right.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Just curious why you would use EDIT and FTOA to convert the A18 column to an A8 column?

I would use Substring:

SUBSTR(inlength, parent, start, end, sublength, outfield)

AG/A8 = SUBSTR(18, STK_NEB, 11, 18, 8, 'A8');


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
Why not convert them both in I8?

And if possible in the master file.
(This only would work if the real data is in fact numeric)




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Robing9
Member
posted Hide Post
hi all

Sloved atlast. Insted of using 
  
SET ASNAMES =ON
DEFINE FILE STK
LDZ/D8.2S=EDIT(STK_NEB);
AG/A8=FTOA(LDZ, '(D8c)', AG);
END


I used 

 
SET LEADZERO=OFF
TABLE FILE STK
PRINT
     COMPUTE AG/A8 = TRIM('L', STK_NEB, 18, '0', 1, 'A8');
     STK_INFO
ON TABLE HOLD AS SPDATA
END


Then use the HOLD file in the Merge it works. But still not sure why the previous way it didnt work.


7.6, Unix, HTML
 
Posts: 18 | Location: Kochi, Kerala, India | Registered: August 26, 2008Report This Post
Tony A
Expert
posted Hide Post
I'd pay a visit to your "data designers" if I were you and preach the benefits of "same name same format" especially when key fields are involved - oh wait - perhaps they've not had training Wink

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Robing9
Member
posted Hide Post
Hi all

Found the reason why it didn't work with
SET ASNAMES =ON
DEFINE FILE STK
LDZ/D8.2S=EDIT(STK_NEB);
AG/A8=FTOA(LDZ, '(D8c)', AG);
END


After the define we have to give a COMPUTE in the TABLE level.
COMPUTE STK_NEB1/A8 = RJUST(8,AG,STK_NEB1);


in both the MERGE tables.(This is to align the data in the SORT BY Field properly).

NOw run the procedure it will work Smiler

in


7.6, Unix, HTML
 
Posts: 18 | Location: Kochi, Kerala, India | Registered: August 26, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] MATCH Command SORT BY ISSUE

Copyright © 1996-2020 Information Builders