topic Serial File Processing in Script in QlikView

Serial File Processing in Script

Tue, 16 Jun 2009 22:35:03 GMT

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.

Serial File Processing in Script

Tue, 16 Jun 2009 22:52:38 GMT

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;

Serial File Processing in Script

rwunderlich — Tue, 16 Jun 2009 23:50:22 GMT

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