Left and Right Columns 1

[johnvd23]

Kind of a new WF Developer here.

I'm trying to figure out a way to organize two tables. Currently the layout looks like this (Fictional data used as an example).

Table A

Left Column Right Column
Boxa Project b
Project c Box d
Project m Box u
Box u Project j
Box m Project u

What I would like is for the Boxes to show up in one Column and the Projects in the other.

Any ideas?

 

[focwizard]

Loads of ideas, but need more info.

First, are the items in each column separate values of the same field, or are they values of two different fields?

Second, what does the data description look like?

Third, do the left column and the right column always have the SAME number of elements?

 

[johnvd23]

fowizard,

This really was a hypothetical example. I'm dealing with sensitive data so I couldn't show a real life example.

1. Each column represents 1 field. The example shows the data as shown in the query the left has a one to one relationship with the right.
2. Of the data type? A25
3. No, if it's a box it has 6 characters and if it's a project it will have 9.

Sorry if these answers are way off, I had a hard time seeing where you were going with your questions.

 

[aceccucci]

I think this is what your looking for, focexec follows:

-* CREATE A HOLD FILE FOR PROJECTS

MATCH FILE A
BY FIELD_LEFT AS PROJ_FIELD
WHERE FIELD_LEFT CONTAINS 'PROJECT'
RUN
FILE A
BY FIELD_RIGHT AS PROJ_FIELD
WHERE FIELD_RIGHT CONTAINS 'PROJECT'
ON TABLE HOLD AS HPROJ
END

-* CREATE A HOLD FILE FOR BOXES

MATCH FILE A
BY FIELD_LEFT AS BOX_FIELD
WHERE FIELD_LEFT CONTAINS 'BOX'
RUN
FILE A
BY FIELD_RIGHT AS BOX_FIELD
WHERE FIELD_RIGHT CONTAINS 'BOX'
ON TABLE HOLD AS HBOX
END

-* CREATE A COMMON JOIN KEY ON THE PROJECTS HOLD FILE

DEFINE FILE HPROJ
CNT_LEFT/I6 = CNT_LEFT +1;
END

TABLE FILE HPROJ
PRINT PROJ_FIELD
BY CNT_LEFT
ON TABLE HOLD AS FPROJ FORMAT FOCUS INDEX CNT_LEFT
END

-* CREATE A COMMON JOIN KEY ON THE BOXES HOLD FILE

DEFINE FILE HBOX
CNT_RIGHT/I6 = CNT_RIGHT +1;
END

TABLE FILE HBOX
PRINT BOX_FIELD
BY CNT_RIGHT
ON TABLE HOLD AS FBOX FORMAT FOCUS INDEX CNT_RIGHT
END

-* JOIN THE TWO HOLD FILES BY THEIR COMMON KEY

JOIN CLEAR *
JOIN CNT_LEFT IN FPROJ TO CNT_RIGHT IN FBOX AS J1

-* PRINT RESULTS

TABLE FILE FPROJ
PRINT PROJ_FIELD BOX_FIELD
BY CNT_LEFT NOPRINT
END

*******************************************************

REPORT PRODUCED:
================


PROJ_FIELD BOX_FIELD
---------- ---------
PROJECT B BOX A
PROJECT C BOX D
PROJECT J BOX M
PROJECT M BOX U
PROJECT U

 

[craigiep]

-* if you're able to identify which data is box and which is project, then something like this may do the job

DEFINE FILE X
LEFT_COL/A25 = IF LEFT_COL CONTAINS 'BOX' THEN LEFT_COL ELSE RIGHT_COL;
RIGHT_COL/A25 = IF RIGHT_COL CONTAINS 'PROJECT' THEN RIGHT_COL ELSE LEFT_COL;
END

TABLE FILE X
PRINT LEFT_COL RIGHT_COL
END

 

[johnvd23]

Thanks that did it!