Serial File Processing in Script

[#1]

Hello. This is probably an easy one, but I'm a bit stuck!

I've got a file that I'm loading in from a csv and I've also created an inline file that joins to it. I want to create a new field in the file that combines information from a field in the csv and a field the inline file.

I tried creating the new field in the code that loads the csv, but, of course, the field from the inline file does not exist at that point.

I need to be able to do additional processing on the joined file to create the new field. Does anyone know how to do this?

Hope that makes sense!

Many thanks.

[#2 · Accepted solution]

TEMP TABLE
1. Load the data from the CSV
2. Inline load and join (left) the data to the CSV

NEW TABLE
3. Complete a nonconcatenate resident load of the data and derive the new field using the necessary fields
4. Drop the temp table created in steps 1 and 2
5 you should now have a table that has all of the info you need.

TEMP_TABLE:
LOAD
 Field_1,
 Field_2,
 Field_3
FROM [test1.csv] (ansi, txt, delimiter is ',', embedded labels, msq);

LEFT JOIN LOAD * INLINE [
 Field_3, Field_4
 aa, test 1
 bb, test 2
 cc, test 3
 dd, test 4
];

FINAL_TABLE:
NOCONCATENATE LOAD
 Field_1,
 Field_2,
 Field_3,
 Field_4,
 Field_1 & '_' & Field_2 & '_' & Field_4 as CombinedField
RESIDENT TEMP_TABLE;

DROP TABLE TEMP_TABLE;

[rwunderlich]

Or you can use a right join. Then there is no need to create a second table or drop anything.

RIGHT JOIN LOAD DISTINCT
 Field_1,
 Field_2,
 Field_3,
 Field_4,
 Field_1 & '_' & Field_2 & '_' & Field_4 as CombinedField
RESIDENT TEMP_TABLE;

--Rob