 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [CLOSED] I don't want the prior row. I want the row before that.
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] I don't want the prior row. I want the row before that. Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] I don't want the prior row. I want the row before that. | Login/Join |
| Member |
So here is my problem. I have at table. The first 3 columns are correct. However, the correct data for that row, is two rows back. For example, on row 10 the first 3 columns are correct the rest of the columns are wrong. The correct data for row 10 is in row 8. In order ti fix the table, I need to move the data down 2 rows. How do I do this using WebFocus.This message has been edited. Last edited by: FP Mod Chuck, WebFOCUS 8 Windows, All Outputs | ||
|
| Virtuoso |
RichM Is this always the case where the data is wrong or is it just the one row? What is the source of the data? Thank you for using Focal Point! Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats | |||
|
| Member |
Correct. It's not just the one row. It's all the rows. WebFOCUS 8 Windows, All Outputs | |||
|
| Virtuoso |
I don’t understand the specification for this question. Is this something you can accomplish in SQL? Could you give us more details on what you want? WebFOCUS 8206, Unix, Windows | |||
|
| Member |
First off. No SQL allowed here. Here is an example of a good record John Doe 100 Main Street Anytown USA Firstname = John Lastname = Doe Street Address = 100 Main Street City = Anytown State = USA Now here is the problem on row 10 is John Doe on row 8 is 100 Main Street Anytown USA The correct data is in 2 different rows. The first few rows will be fixed manually. The rest of the rows need to be moved down. It's all ways two rows off. WebFOCUS 8 Windows, All Outputs | |||
|
| Virtuoso |
Do you have a key of foreign key to use that can tell you which records goes with which one ? If so, you can then extract them per that id/fk and have them "grouped" properly. It sounds to me that your table contain multiple type of record : address row, name row, etc. Probably there is a rowType indicator and a common fk somewhere but without further information it's difficult to help. We can only do assumption. Please share your master file. It may help to understand how your data is organized. WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
| Master |
Solution one: You could query the data once. and add counter ( COMPUTE COUNTER/I11 = COUNTER + 1; ) Hold as H01 query over H01 hold as H02 with COUNTER_X/I11 = COUNTER + 2; DEFINE FILE H01 COUNTER_X/I11 = COUNTER END Then MATCH H01 and H02 with COUNTER_X as BY field. Solution two: COMPUTE LAST_ADDRESS/A40 = LAST ADDRESS; COMPUTE LAST_LAST_ADDRESS/A40 = LAST LAST_ADDRESS; G'luck _____________________ WF: 8.0.0.9 > going 8.2.0.5 | |||
|
| Virtuoso |
In my considered opinion, you're trying to use a BI tool as an ETL tool. This is a data problem that begs the question, why is my data set up like this and even if you come up with something that fixes the problem today, will it be repeatable next week? WebFOCUS 8206, Unix, Windows | |||
|
| Virtuoso |
Rich, Is this something you could work with (using the CAR file)? TABLEF FILE CAR PRINT COUNTRY CAR MODEL ON TABLE HOLD END DEFINE FILE HOLD LL1/I5=LAST LL1 + 1; LL2/I5=LL1 - 2; MOD1/A30=LAST MODEL; MOD2/A30=LAST MOD1; MODL/A30=IF LL2 LE 0 THEN MODEL ELSE MOD2; END TABLE FILE HOLD PRINT MODEL LL1 LL2 MOD1 MOD2 MODL BY COUNTRY BY CAR END Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
| Guru |
Isn't there a anothe rkey in the table to find the unique record? For instance a addresstypeid or something? Test: WF 8.2 Prod: WF 8.2 DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena. | |||
|
| Expert |
This sounds more like a data cleaning issue then something to be fixed in a fex. | |||
|
| Expert |
IBI Offers this as a service. Check it out The Real Cost of Bad Data: Six Simple Steps to Address Data Quality Issues and there's more... | |||
|
| Expert |
 This was taken from "The Data Monetization Mindset" by Rado Kotorov of IBI. Link | |||
|
| Master |
We solve a lot on fex level. Often just to find out what the businessrules should be or to find out what the business really want. And to help find out what's wrong with the data. Prototyping. Higher data quality takes a LOT of time. So is implementing some of it on database level. I do agree data quality is highly unrated topic. But this is not the place to preach about it. _____________________ WF: 8.0.0.9 > going 8.2.0.5 | |||
|
| Guru |
It's great to be able to do this kind of logic in the fex, but it requires thorough insight in the data. If we take this example: John Doe 100 Main Street Anytown USA TS has to check if there could also be these cases in the data: John Doe 100 Main Street Anytown USA John Doe PLC Office 123 100 Main Street Anytown USA etc etc. A simple implementation of a businessrule in the fex can grow to a very complex piece of processing. Test: WF 8.2 Prod: WF 8.2 DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena. | |||
|
| Master |
Not sure if this helps or not, but there is the PARTITION_REF function that you might be able to use. Docs: PARTITION_REF: Using Prior Field Values in Calculations HallwayProd: 8202M1 Test: 8202M4 Repository:  OS:  Outputs:     | |||
|
| Platinum Member |
Wow - I sure could have used PARTITION_REF a few years ago when I was developing a system that had to keep track of records from N rows previous. Even though that project was eight years ago, I still have lots of data I can use to rewrite those functions and test PARTITION_REF out. WebFOCUS 8.2.03 - Production WebFOCUS 8.2.04 - Sand Box Windows 2012 R2 Server HTML, PDF, Excel In FOCUS since 1980 | |||
|
| Master |
I went back and looked at the docs of previous versions, and I should add the caveat that this function is available beginning in release 8201M - About This Release If you want to see what functions are available in your release you can query that information from the WF System Tables: SYSSQLOP: Reporting on Function Information
TABLE FILE SYSSQLOP
SUM
FUNCTION_SYNTAX
FUNCTION_DESC
FUNCTION_EXAMPLE
BY CATEGORY
BY FUNCTION
BY FUNCTION_TYPE
BY OPTIMIZE
WHERE CATEGORY EQ &CATEGORY.(OR(FIND CATEGORY IN SYSSQLOP |FORMAT=A128)).Category.;
WHERE SUFFIX EQ &SUFFIX.(OR(FIND SUFFIX IN SYSSQLOP |FORMAT=A25)).Adapter.;
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE SET STYLE *
TYPE=REPORT, LINES-PER-PAGE=UNLIMITED, TITLETEXT='WebFOCUS Functions', $
ENDSTYLE
END
-RUN
HallwayProd: 8202M1 Test: 8202M4 Repository: OS: Outputs: | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] I don't want the prior row. I want the row before that.
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] I don't want the prior row. I want the row before that.Copyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |