Has anyone wrote a Data Migrator flow where flow created column data from row data?

I have a two columns source table with

Key1 name1
Key1 name2
Key1 name3
Key2 name1
Key2 name2... etc etc

I have to move this into a two column table

Key1 name1 || name2 || name3
Key2 name1 || name2 || name3
Key3 name1 || name2 || name3.. etc etc

any help appreciated,
Endre

This message has been edited. Last edited by: Endre,

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

...
...
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.


Posted by clif