any help appreciated, EndreThis message has been edited. Last edited by: Endre,
WebFocus 7.6.8 iWay Data Migrator 7.6.8 PMF 5.1
November 06, 2008, 09:52 AM
Here is one idea that might be useful:
-* File rowstocols.fex SET HOLDLIST=PRINTONLY -SET &NN=' '; TABLE FILE CAR SUM CNT.DST.COUNTRY ON TABLE HOLD AS HOW_MANY FORMAT ALPHA END -RUN -READ HOW_MANY &NN.I5 -TYPE HOW MANY: &NN TABLE FILE CAR SUM COUNTRY BY BODYTYPE ACROSS COUNTRY NOPRINT ON TABLE SET PAGE-NUM OFF ON TABLE SET BYDISPLAY ON ON TABLE NOTOTAL ON TABLE HOLD FORMAT FOCUS END ?FF HOLD -SET &CONCAT = IF &NN EQ 1 THEN 'E02' ELSE - IF &NN EQ 2 THEN 'E02 || E03' ELSE - IF &NN EQ 3 THEN 'E02 || E03 || E04' ELSE - IF &NN EQ 4 THEN 'E02 || E03 || E04 || E05' ELSE - IF &NN EQ 5 THEN 'E02 || E03 || E04 || E05 || E06' ; DEFINE FILE HOLD NEW_COL_TWO/A500= &CONCAT ; END TABLE FILE HOLD PRINT NEW_COL_TWO BY E01 END
November 13, 2008, 01:58 PM
... ... also....
How to pivot a table containing name and value pairs
While most input data is represented in a columnar structure, it’s also possible to represent data with name and value pairs. This is common for XML structures but is also used for flat files.
It could also be used with a relational database. The same three column table could be used to store everything. But that would be madness. When input data is represented this way, it’s necessary to “pivot” the data to load it into a normal relational table.
For example consider the following table: 1Last Name ADAMKIEWICZ 1First Name GREGORY 1Position Line Worker 2Last Name ADAMS 2First Name GAY 2Position Line Worker 3Last Name ALBOR 3First Name STEVEN 3Position Technician 4Last Name ALSMAN 4First Name RANDY 4Position Line Worker 5Last Name ARNOLD 5First Name HAROLD 5Position Technician 6Last Name AUSTIN 6First Name BARRY 6Position Technician 7Last Name BALMER 7First Name PETER 7Position Technician 8Last Name BALNAVE 8First Name WILLIAM 8Position Line Manager
The first column of each table is an ID number, the second is a Name such as “Last Name” or “First Name” and the third column is the value. This file could be described by the following synonym:
FILENAME=hrflat, SUFFIX=FIX , DATASET=baseapp/hrflat.ftm, $ SEGMENT=HRFLAT, SEGTYPE=S0, $ FIELDNAME=ID_NUM, ALIAS=ID_NUM, USAGE=I2, ACTUAL=A2, $ FIELDNAME=PNAME, ALIAS=PNAME, USAGE=A13, ACTUAL=A13, $ FIELDNAME=PVALUE, ALIAS=PVALUE, USAGE=A12, ACTUAL=A12, $ In order to load the data into a relational database, the table must be pivoted, or flipped, with the result:
ID_NUM Last Name First Name Position 1 ADAMKIEWICZ GREGORY Line Worker 2 ADAMS GAY Line Worker 3 ALBOR STEVEN Technician 4 ALSMAN RANDY Line Worker 5 ARNOLD HAROLD Technician 6 AUSTIN BARRY Technician 7 BALMER PETER Technician 8 BALNAVE WILLIAM Line Manager As it happens, this type of pivot is trivial to in FOCUS using TABLE and the verb ACROSS:
TABLE FILE HRFLAT WRITE PVALUE BY ID_NUM ACROSS PNAME ON TABLE HOLD FORMAT ALPHA END The disadvantage of this approach is that it requires writing a TABLE request, there’s no way to do this in a DM flow, so using this data would be two-step process, first run the TABLE to create a flat file, then load it into a relational table. Also, the synonym that is generated has a column of PVALUE for all three columns. That would mean editing the generated synonym to enter the desired column names. It’s possible to write a SQL SELECT statement to do the same thing.
SELECT ID_NUM , MAX( CASE WHEN PNAME = 'Last Name' THEN PVALUE END AS "Last Name" , MAX( CASE WHEN PNAME = 'First Name' THEN PVALUE END ) AS "First Name" , MAX( CASE WHEN PNAME = 'Position' THEN PVALUE END ) AS "Position" FROM HRFLAT T1 GROUP BY ID_NUM
This could even be done using the DataMigrator user interface in the Column Selection grid:
The disadvantage of this approach is that you need to know all the possible values for the name column to write the select statement. However once you do that, the synonym that is generated has the correct column names.