Focal Point
[CLOSED] I don't want the prior row. I want the row before that.

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5977008096

September 27, 2018, 01:09 PM
RichM
[CLOSED] I don't want the prior row. I want the row before that.
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
September 27, 2018, 02:54 PM
FP Mod Chuck
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
September 27, 2018, 10:37 PM
RichM
Correct. It's not just the one row. It's all the rows.


WebFOCUS 8
Windows, All Outputs
September 28, 2018, 07:14 AM
BabakNYC
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
September 28, 2018, 07:41 AM
RichM
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
September 28, 2018, 08:29 AM
MartinY
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
September 28, 2018, 08:37 AM
Dave
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
September 28, 2018, 08:44 AM
BabakNYC
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
September 30, 2018, 07:19 AM
Danny-SRL
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

October 01, 2018, 06:03 AM
Frans
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.
October 01, 2018, 03:02 PM
Doug
This sounds more like a data cleaning issue then something to be fixed in a fex.
October 01, 2018, 03:06 PM
Doug
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...
October 01, 2018, 03:35 PM
Doug

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

October 02, 2018, 02:06 AM
Dave
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
October 02, 2018, 02:57 AM
Frans
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.
October 02, 2018, 10:51 AM
Hallway
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:
 
 
 
 
October 02, 2018, 11:01 AM
Michael L Meagher
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
October 02, 2018, 11:37 AM
Hallway
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: