My destinations: CREATE TABLE TBL_1 (DT DATETIME ); CREATE TABLE TBL_2 (INTG INT ); CREATE TABLE TBL_3 (TXT VARCHAR(30)); Source (one file, no header, pipe delimiter): TYPE1| 2018-08-13 00:00:00 TYPE2|1234 TYPE1| 2018-07-17 00:00:00 TYPE3|SOME TEXT Job will read the file and create three different flow. Each flow should go to the corresponding table. The first field has hardcoded type information. “TYPE1” should go to TBL_1, “TYPE2” should go to TBL_2 and TYPE3 should go to the TBL_3 table.
7 Community Answers
Ian Funnell —
Yes, Matillion certainly does have Split components:
Lets say TBL_1 has 20 columns, TBL_2 has 50 columns and TBL_3 has 150 columns. All columns are different types. You recommend create a table with 150 text columns and then use a Transformation job with vertical filters? Thank you, Dmitriy
STEP 1 starts with a source table with just one column that has relevant data. Use a calculator to create a new column that has the relevant TYPE for each row. The use a Filter for each TYPE and persist results to a respective tables. A REWRITE TABLE may be suitable for the last step.
STEP 2. You may repeat this for each table you create in STEP1.
Start with a TYPE-table, use SPLIT component to break out into relevant columns and write to the relevant target table.
I haven’t tried this but If you may be able to get away with just a single transformation job for STEP2 which is parameterised using Grid variables with relevant metadata.