Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] I don't want the prior row. I want the row before that.
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] I don't want the prior row. I want the row before that.
 Login/Join
 
Member
posted
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
 
Posts: 12 | Registered: June 01, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1913 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Correct. It's not just the one row. It's all the rows.


WebFOCUS 8
Windows, All Outputs
 
Posts: 12 | Registered: June 01, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1847 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 12 | Registered: June 01, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1847 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 1960 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 435 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
This sounds more like a data cleaning issue then something to be fixed in a fex.
 
Posts: 3103 | Location: Middle Tennessee | Registered: February 23, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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...
 
Posts: 3103 | Location: Middle Tennessee | Registered: February 23, 2005Reply With QuoteReport This Post
Expert
posted Hide Post

This was taken from "The Data Monetization Mindset" by Rado Kotorov of IBI. Link

 
Posts: 3103 | Location: Middle Tennessee | Registered: February 23, 2005Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 435 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Master
posted Hide Post
quote:
However, the correct data for that row, is two rows back


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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 604 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Seattle, WA | Registered: April 07, 2015Reply With QuoteReport This Post
Master
posted Hide Post
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

  


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 604 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] I don't want the prior row. I want the row before that.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.