I have a list of employees , hired over various times. Im...

[Russian Wyatt 2]

I have a list of employees , hired over various times. Im tying to trace which employees filled which positions.

I started off with a data set that looks like this (ID is the Key field)

MODIFY FILE Hire_list

FIXFORM Pos/I2 ID/A3 Name/A60 Replaced/A3

Pos ID Name Replaced

1 JS7 John Smith

2 JD9 Jane Doe JS7

3 WP7 Walter Pharaoh JD9

4 DJ9 Daniel James

5 MF5 Martin Franco

6 IT1 Ivory Thomas MF5

 

This shows that John was replaced by Jane who was replaced by Walter. The idea being that it was all one position and set that all to the same position number. The same with Ivory replacing Martin.

In the end I want it to look like this:

Pos ID Name Replaced

1 JS7 John Smith

1 JD9 Jane Doe JS7

1 WP7 Walter Pharaoh JD9

4 DJ9 Daniel James

5 MF5 Martin Franco

5 IT1 Ivory Thomas MF5

 

I just cant seem to wrap my head around the logic to create this in Modify. any suggestions

[David Briars]

Russian:

 

I just cant seem to wrap my head around the logic

 

 

Maybe try something like this:

COMPUTE NEW_POS/I2 = IF REPLACED EQ ' ' THEN POS ELSE LAST NEW_POS;

I will typically do the Extract and Transform processes with a TABLE FILE command, and then do the Load process with the MODIFY command.

The TABLE can write out to a HOLD file, and then you FIXFORM in the HOLD file in the MODIFY.

This can assist in simplifying the MODIFY. Just a thought, for sure not the only way to do an ETL.

[David Beagan]

The following code seems to do what you are asking. I look at the data in levels, John, Jane and Martin are level 1. Jane and Ivory are level 2. Walter is level 3. A level 1 row is the parent of a level 2 row, etc.

The idea is to join hier_list to itself to get the level 1 rows in a parent hold file. Then each time through the -REPEAT loop, hire_list is joined to a parent hold file to pick up the parents POS for the child. At the end of the loop, HOLD AS MODDATA FORMAT ALPAHA appends all children IDs with the inherited parent POS. In this way, a file of POS,ID values is built up, which then are used by the MODIFY FILE to make the updates. The name field doesnt need to be involved in this, I just put it in there to see better what is going on.

FILEDEF MODDATA DISK moddata.txt (APPEND

 

TABLE FILE hire_list

PRINT POS

ID

NAME

WHERE REPLACED IS MISSING

ON TABLE HOLD AS parent FORMAT FOCUS INDEX ID

END

-RUN

 

-REPEAT EndREPEAT WHILE &RECORDS NE 0

JOIN CLEAR J1

JOIN REPLACED IN hire_list TO ALL ID IN parent TAG par AS J1

 

TABLE FILE hire_list

PRINT par.POS

ID

NAME

ON TABLE HOLD AS parent FORMAT FOCUS INDEX ID

END

HOLD AS MODDATA FORMAT ALPHA

-RUN

-EndREPEAT

 

!type moddata.txt

 

MODIFY FILE hire_list

FIXFORM FROM MODDATA

MATCH ID

ON MATCH UPDATE POS

ON NOMATCH REJECT

DATA ON MODDATA

END