Add missing data from temp tables

[Glenn_Renwick]

Hi,

I have a 4 tables

Table1:

LOAD *

,'Table 1' as Table

INLINE [

    ID1, M_Field1

    A, MF1_1

    B, MF1_2

    C, MF1_3

    D, MF1_4

    E, MF1_5

    F, MF1_6

];

Concatenate(Table1)

LOAD *

,'Table 2' as Table

INLINE [

    ID1, ID2

    A, R1

    B, R2

    C, R3

];

Concatenate(Table1)

LOAD *

,'Table 3' as Table

INLINE [

    ID2, ID3,M_Field1, M_Field2

    R1, C1, MF5_1, MF6_1

    R2, C2, MF5_2, MF6_2

    R3, C3, MF5_3, MF6_3

    R4, C4, MF5_4, MF6_4

    R5, C5, MF5_5, MF6_5

    R6, C6, MF5_6, MF6_6

];

Concatenate(Table1)

LOAD *

,'Table 4' as Table

INLINE [

   ID1, ID2, M_Field1, M_Field2

    C, R3, MF3_XX, MF4_XX

    G, R4, MF3_4, MF4_4

    H, R5, MF3_5, MF4_5

    I, R6, MF3_6, MF4_6

    ];

That need to be concatenated.

When you view this in a simple table chart you will see many missing Field# Values.

I would like to fill in the missing values with the values within the other tables when you can link through a combination of the IDs.

For example:

//Fill table 2 two with information from table 1

temp1:

LOAD Distinct

   ID1,

   M_Field1

Resident Table1

Where Table = 'Table 1';

left join (Table1)

LOAD

    ID1,

     M_Field1 as M_Field1_ADDED

Resident temp1;

drop table temp1;

drop fields M_Field1 from Table1;

RENAME field M_Field1_ADDED to M_Field1;

However this removes M_Field1 where no match was found in the join which is no good (In table 3)

I'm looking for a nice technique to just pull data from the main table and join it back onto IDs where no values already exist.

Thanks

[jonathandienst]

Hi

In this case you may want to join those tables rather than concatenate them. Each successive load is extending the original rows by adding new attributes, rather than adding new entities (ie rows).

This should achieve what I think you are trying to do. You would need to choose the join type for your model (outer, left, right or inner)

HTH

Jonathan