Focal Point
[SOLVED] Duplicating / comparing values in the same table

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

August 26, 2016, 06:09 AM
Jon FB
[SOLVED] Duplicating / comparing values in the same table
Hi Folks,

I might not be able to full describe my issue, but I will give as much information as possible
I can't translate this into CAR but I will post the .MAS and fex's I am using.

Here is my .MAS file for the table FTE_LOAD

 FILENAME=FTE_LOAD, SUFFIX=FOC     , $
  SEGMENT=SEG01, SEGTYPE=S1, $
    FIELDNAME=DELIVS_KEY, ALIAS=E01, USAGE=A6, FIELDTYPE=I,
      MISSING=ON, $
    FIELDNAME=DELIVS_NAME, ALIAS=E02, USAGE=A50,
      MISSING=ON, $
  SEGMENT=SEG02, SEGTYPE=S1, PARENT=SEG01, $
    FIELDNAME=DBDS_KEY, ALIAS=E03, USAGE=A6, FIELDTYPE=I,
      MISSING=ON, $
    FIELDNAME=DBDS_NAME, ALIAS=E04, USAGE=A100,
      MISSING=ON,
      TITLE='DBDS_NAME', $
    FIELDNAME=DBDS_NAME_TAR, ALIAS=E05, USAGE=A30,
      MISSING=ON,
      TITLE='DBDS_NAME_TAR', $
    FIELDNAME=DBDS_IARU, ALIAS=E06, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=COMDEF1, ALIAS=E07, USAGE=A72,
      MISSING=ON, $
    FIELDNAME=COMDEF2, ALIAS=E08, USAGE=A195,
      MISSING=ON, $
    FIELDNAME=COMDEF3, ALIAS=E09, USAGE=A203,
      MISSING=ON, $
    FIELDNAME=COMDEF4, ALIAS=E10, USAGE=A133,
      MISSING=ON, $
    FIELDNAME=STADEF1, ALIAS=E11, USAGE=A71,
      MISSING=ON, $
    FIELDNAME=STADEF2, ALIAS=E12, USAGE=A108,
      MISSING=ON, $
    FIELDNAME=STADEF3, ALIAS=E13, USAGE=A95,
      MISSING=ON, $
    FIELDNAME=STADEF4, ALIAS=E14, USAGE=A87,
      MISSING=ON, $
    FIELDNAME=CONDEF1, ALIAS=E15, USAGE=A80,
      MISSING=ON, $
    FIELDNAME=CONDEF2, ALIAS=E16, USAGE=A88,
      MISSING=ON, $
    FIELDNAME=CONDEF3, ALIAS=E17, USAGE=A90,
      MISSING=ON, $
    FIELDNAME=CONDEF4, ALIAS=E18, USAGE=A131,
      MISSING=ON, $
    FIELDNAME=CERDEF1, ALIAS=E19, USAGE=A99,
      MISSING=ON, $
    FIELDNAME=CERDEF2, ALIAS=E20, USAGE=A157,
      MISSING=ON, $
    FIELDNAME=CERDEF3, ALIAS=E21, USAGE=A138,
      MISSING=ON, $
    FIELDNAME=CERDEF4, ALIAS=E22, USAGE=A121,
      MISSING=ON, $
  SEGMENT=SEG03, SEGTYPE=S1, PARENT=SEG02, $
    FIELDNAME=DBDR_KEY, ALIAS=E23, USAGE=A7, FIELDTYPE=I,
      MISSING=ON, $
    FIELDNAME=SERIAL_NOA, ALIAS=E24, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=SERIAL1, ALIAS=E25, USAGE=A25,
      MISSING=ON,
      TITLE='SERIAL1', $
    FIELDNAME=DESCRIPTION, ALIAS=E26, USAGE=A161,
      MISSING=ON,
      TITLE='DESCRIPTION', $
    FIELDNAME=FREENOTES, ALIAS=E27, USAGE=A3000,
      MISSING=ON,
      TITLE='FREENOTES', $
  SEGMENT=SEG04, SEGTYPE=S1, PARENT=SEG03, $
    FIELDNAME=IM_KEY, ALIAS=E28, USAGE=A5, FIELDTYPE=I,
      MISSING=ON, $
    FIELDNAME=LFSTEP, ALIAS=E29, USAGE=A5,
      MISSING=ON, $
    FIELDNAME=GATE, ALIAS=E30, USAGE=A6,
      MISSING=ON, $
    FIELDNAME=GATEDESC, ALIAS=E31, USAGE=A50,
      MISSING=ON,
      TITLE='GATEDESC', $
    FIELDNAME=GATEINDEX, ALIAS=E32, USAGE=A2,
      MISSING=ON, $
    FIELDNAME=COMP, ALIAS=E33, USAGE=I4,
      MISSING=ON, $
    FIELDNAME=STAB, ALIAS=E34, USAGE=I4,
      MISSING=ON, $
    FIELDNAME=CONS, ALIAS=E35, USAGE=I4,
      MISSING=ON, $
    FIELDNAME=CERT, ALIAS=E36, USAGE=I4,
      MISSING=ON, $
    FIELDNAME=COMP_T, ALIAS=E37, USAGE=D6.1,
      MISSING=ON, $
    FIELDNAME=STAB_T, ALIAS=E38, USAGE=D6.1,
      MISSING=ON, $
    FIELDNAME=CONS_T, ALIAS=E39, USAGE=D6.1,
      MISSING=ON, $
    FIELDNAME=CERT_T, ALIAS=E40, USAGE=D6.1,
      MISSING=ON, $


Here is a a snippet of the table file requests i am making for this case:
 DEFINE FILE FTE_LOAD
CON_L_G/A40 = LFSTEP | ' ' | GATE;
END
TABLE FILE FTE_LOAD
PRINT
COMP_T AS COMP_T1
STAB_T AS STAB_T1
CONS_T AS CONS_T1
CERT_T AS CERT_T1
BY DELIVS_KEY
BY DBDS_KEY
BY CON_L_G
WHERE DBDS_KEY EQ 'D10001';
END

TABLE FILE FTE_LOAD
PRINT
COMP
STAB
CONS
CERT
BY DELIVS_KEY
BY DBDS_KEY
BY CON_L_G
WHERE DBDS_KEY EQ 'D10001';
END


Output:

First table:
  
DELIVS_KEY|DBDS_KEY|DBDR_KEY |IM_KEY |CON_L_G|COMP_T1|STAB_T1|CONS_T1|CERT_T1 
D10001    | D10001 | D100001 | D1002 | ESPR  | 4.0   | 4.0   | 4.0   | 2.5 
D10001    | D10001 | D100001 | D1006 | T0+2  | 4.0   | 4.0   | 4.0   | 2.5 
D10001    | D10001 | D100001 | D1007 | T0    | 4.0   | 4.0   | 4.0   | 2.5 
D10001    | D10001 | D100002 | D1009 | ESPR  | .     | .     | .     | . 
D10001    | D10001 | D100002 | D1013 | T0+2  | .     | .     | .     | . 
D10001    | D10001 | D100002 | D1014 | T0    | .     | .     | .     | .


Second Table: 
  
DELIVS_KEY|DBDS_KEY|DBDR_KEY|IM_KEY|CON_L_G|COMP|STAB|CONS|CERT 
D10001    |D10001  |D100001 |D1002 |ESPR   |0   |0   |0   |0 
D10001    |D10001  |D100001 |D1006 |T0+2   |0   |0   |0   |0 
D10001    |D10001  |D100001 |D1007 |T0     |4   |4   |4   |4 
D10001    |D10001  |D100002 |D1009 |ESPR   |0   |0   |0   |0 
D10001    |D10001  |D100002 |D1013 |T0+2   |0   |0   |0   |0 
D10001    |D10001  |D100002 |D1014 |T0     |4   |2   |4   |4


Table view straight from the database:
  
DELIVS_KEY|DBDS_KEY|DBDR_KEY|IM_KEY|CON_L_G|COMP|STAB|CONS|CERT|COMP_T1|STAB_T1|CONS_T1|CERT_T1  
D10001    |D10001  |D100001 |D1002 |ESPR   |0   |0   |0   |0   | 4.0   | 4.0   | 4.0   | 2.5 
D10001    |D10001  |D100001 |D1006 |T0+2   |0   |0   |0   |0   | 4.0   | 4.0   | 4.0   | 2.5 
D10001    |D10001  |D100001 |D1007 |T0     |4   |4   |4   |4   | 4.0   | 4.0   | 4.0   | 2.5 
D10001    |D10001  |D100002 |D1009 |ESPR   |0   |0   |0   |0   | .     | .     | .     | . 
D10001    |D10001  |D100002 |D1013 |T0+2   |0   |0   |0   |0   | .     | .     | .     | . 
D10001    |D10001  |D100002 |D1014 |T0     |4   |2   |4   |4   | .     | .     | .     | .

Constraints:
I am not able to change the data unless there is no other alternative solution to this problem unfortunately

Data Explained:
The DELIVS_KEY contains MANY DBDS_KEY
The DBDS_KEY contains MANY DBDR_KEY
The DBDR_KEY contains MANY IM_KEY
Each DBDR_KEY contains a static number of IM_KEYS (7, but for this example i have only shown 3 for simplicity)
Each record contains: DELIVS_KEY | DBDS_KEY | DBDR_KEY | IM_KEY | CON_L_G |COMP|STAB|CONS|CERT|COMP_T1|STAB_T1|CONS_T1|CERT_T1
The top level record (First record) in each DBDS_KEY contains values against the XXXX_T1 fields (see table 1)
So the DBDS_KEY / DBDR_KEY / IM_KEY is known for each record that contains a value for XXXX_T1

Problem:
The XXXX_T1 fields are targets, the user matches the values from COMP / STAB etc against the respective target.
I need to be able to compare the COMP / STAB etc field with the target fields when that target value doesn't exist in that record

How can I go about either comparing the values from the Second table to the first set of values in the top table? to meet logical conditions that the user has requested
In otherwords; how can i compare the values in the records with DBDR_KEY : D100002 for STAB / CONS / COMP etc to the target values in DBDR_KEY: D100001 STAB_T1 / CONS_T1 etc

Hopefully this illustrates my problem well enough, i have only been working with WebFOCUS for a few months now so a clear solution may not be obvious to me.

Many thanks.

-Jon

This message has been edited. Last edited by: Jon FB,


WebFOCUS 8.04
August 26, 2016, 11:04 AM
eric.woerle
Jon,

I guess I don't understand what your actual problem is... From what I can gather you have a multi segment file coming from a singular table. In both of your Table requests you are accessing the 4th segment. So you are accessing all of the segments of the table. I don't see a need for 2 separate requests.

wouldn't something like the below be simpler?
DEFINE FILE FTE_LOAD
CON_L_G/A40 = LFSTEP | ' ' | GATE;
END
TABLE FILE FTE_LOAD
PRINT
COMP_T AS COMP_T1
STAB_T AS STAB_T1
CONS_T AS CONS_T1
CERT_T AS CERT_T1
COMP
STAB
CONS
CERT
COMPUTE CERT_COMPARE/I8= IF CERT EQ CERT_T THEN [DO THIS] ELSE [DO THAT];
BY DELIVS_KEY
BY DBDS_KEY
BY CON_L_G
WHERE DBDS_KEY EQ 'D10001';
END



Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
August 30, 2016, 02:41 AM
Jon FB
Hi Eric,

Yes, the 2 table requests was a little misleading sorry.
The problem is that my table file request gives me, for example 3 records, but only 1 of those 3 records contains values in the COMP_T1 / STAB_T1 fields. I need to compare all 3 values in COMP to that 1 value in COMP_T1.
 
DELIVS_KEY|DBDS_KEY|DBDR_KEY|IM_KEY|CON_L_G|COMP|STAB|CONS|CERT|COMP_T1|STAB_T1|CONS_T1|CERT_T1  
D10001    |D10001  |D100001 |D1002 |ESPR   |0   |0   |0   |0   | 4.0   | 4.0   | 4.0   | 2.5 
D10001    |D10001  |D100001 |D1006 |T0+2   |0   |0   |0   |0   | 4.0   | 4.0   | 4.0   | 2.5 
D10001    |D10001  |D100001 |D1007 |T0     |4   |4   |4   |4   | 4.0   | 4.0   | 4.0   | 2.5 
D10001    |D10001  |D100002 |D1009 |ESPR   |0   |0   |0   |0   | .     | .     | .     | . 
D10001    |D10001  |D100002 |D1013 |T0+2   |0   |0   |0   |0   | .     | .     | .     | . 
D10001    |D10001  |D100002 |D1014 |T0     |4   |2   |4   |4   | .     | .     | .     | .

In this view of data, I need to compare the values in DBDR_KEY: D100002 COMP/STAB/CONS/CERT to DBDR_KEY: D100001 COMP_T1/STAB_T1/CONS_T1/CERT_T1

Hopefully this helps explain the issue more clearly.
Please let me know if you need any more clarity

Many Thanks


WebFOCUS 8.04
August 31, 2016, 11:12 AM
Tony A
Jon,

Looking at your data and what you say that you need, you are basically saying that you want to be able to correlate the COMP, STAB, CONS and C|ERT base values at DELIVS_KEY, DBDS_KEY and CON_L_G (defined field).

Therefore, you just need to aggregate (using FST. or MAX. etc.) within those keys to get what you need.

The next question I have is, are you using the GUI or hand coding? If GUI, then which one? Dev Studio, App Studio or IA.

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 
August 31, 2016, 11:49 AM
Tony A
From your first post, I'll assume that you are hand coding this.

Try the following code - which is known as a multi-verb request - to aggregate the *_T1 columns at a higher level and before the compute comparisons.

TABLE FILE FTE_LOAD
  SUM MAX.COMP_T AS COMP_T1
      MAX.STAB_T AS STAB_T1
      MAX.CONS_T AS CONS_T1
      MAX.CERT_T AS CERT_T1
   BY DELIVS_KEY
   BY DBDS_KEY
   BY CON_L_G
  SUM COMP
      STAB
      CONS
      CERT
      COMPUTE COMP_COMPARE/I8 = IF COMP EQ C1 THEN 1 ELSE 0;
      COMPUTE STAB_COMPARE/I8 = IF STAB EQ C2 THEN 1 ELSE 0;
      COMPUTE CONS_COMPARE/I8 = IF CONS EQ C3 THEN 1 ELSE 0;
      COMPUTE CERT_COMPARE/I8 = IF CERT EQ C4 THEN 1 ELSE 0;
   BY DELIVS_KEY
   BY DBDS_KEY
   BY CON_L_G
   BY DBDR_KEY
   BY IM_KEY
WHERE DBDS_KEY EQ 'D10001';
END
-RUN

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 
August 31, 2016, 05:28 PM
eric.woerle
Ok Jon,

I think I understand better. How do you know that you need to compare D100001 to D100002? Is it based on comparing one value to the one sequentially before it? Also are you always comparing the CON_L_G value "T0" against the previous CON_L_G Value "T0"?

Ultimately I think you want to look at the LAST function. This will allow you to compare a record with the one before it. Then it becomes an issue of organizing your records properly.

For the purposes of this example, I'm going to assume that the IM_KEY is irrelevant as I don't see them repeating in your data sample. So I would construct the query something like this:

 
-* I Generally like to pull the data and sort it as needed so I know that my records are in the order I need them.  Theoretically you should be ok using computes... Still I 
-* like to be sure

DEFINE FILE FTE_LOAD
CON_L_G/A40 = LFSTEP | ' ' | GATE;
END

TABLE FILE FTE_LOAD
PRINT COMP_T
      STAB_T
      CONS_T
      CERT_T[
      COMP
      STAB
      CONS
      CERT
BY DELIVS_KEY
BY DBDS_KEY
BY CON_L_G
WHERE DBDS_KEY EQ 'D10001'
ON TABLE HOLD AS MY_ORDER 
END

DEFINE FILE MY_ORDER
INDX/
TABLE FILE MY_ORDER
PRINT COMPUTE COMPARE_COMP/I4=IF DELIVS_KEY EQ LAST DELIVS_KEY AND DBDS_KEY EQ LAST DBDS_KEY AND CON_L_G EQ LAST CON_L_G AND COMP EQ LAST COMP THEN 1 ELSE 0;
      COMPUTE COMPARE_STAB/I4=IF DELIVS_KEY EQ LAST DELIVS_KEY AND DBDS_KEY EQ LAST DBDS_KEY AND CON_L_G EQ LAST CON_L_G AND STAB EQ LAST STAB THEN 1 ELSE 0;
      COMPUTE COMPARE CONS/I4=IF DELIVS_KEY EQ LAST DELIVS_KEY AND DBDS_KEY EQ LAST DBDS_KEY AND CON_L_G EQ LAST CON_L_G AND CONS EQ LAST CONS THEN 1 ELSE 0;
      COMPUTE COMPARE_CERT/I4=IF DELIVS_KEY EQ LAST DELIVS_KEY AND DBDS_KEY EQ LAST DBDS_KEY AND CON_L_G EQ LAST CON_L_G AND CERT EQ LAST CERT THEN 1 ELSE 0;.
      COMPUTE COMPARE_COMP_T/I4=IF DELIVS_KEY EQ LAST DELIVS_KEY AND DBDS_KEY EQ LAST DBDS_KEY AND CON_L_G EQ LAST CON_L_G AND COMP_T EQ LAST COMP_T THEN 1 ELSE 0;
      COMPUTE COMPARE_STAB_T/I4=IF DELIVS_KEY EQ LAST DELIVS_KEY AND DBDS_KEY EQ LAST DBDS_KEY AND CON_L_G EQ LAST CON_L_G AND STAB_T EQ LAST STAB_T THEN 1 ELSE 0;
      COMPUTE COMPARE CONS_T/I4=IF DELIVS_KEY EQ LAST DELIVS_KEY AND DBDS_KEY EQ LAST DBDS_KEY AND CON_L_G EQ LAST CON_L_G AND CONS_T EQ LAST CONS_T THEN 1 ELSE 0;
      COMPUTE COMPARE_CERT_T/I4=IF DELIVS_KEY EQ LAST DELIVS_KEY AND DBDS_KEY EQ LAST DBDS_KEY AND CON_L_G EQ LAST CON_L_G AND CERT_T EQ LAST CERT_T THEN 1 ELSE 0;
BY DELIVS_KEY
BY DBDS_KEY
BY CON_L_G
END



Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
September 02, 2016, 04:34 AM
Jon FB
Hi both,

Thanks very much for the responses.
I have managed to find a working solution using Tony's example


WebFOCUS 8.04