Add missing data from temp tables

Glenn_Renwick — Tue, 28 Oct 2014 14:57:20 GMT

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

Re: Add missing data from temp tables

jonathandienst — Tue, 28 Oct 2014 15:01:51 GMT

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

topic Add missing data from temp tables in QlikView

Add missing data from temp tables

Re: Add missing data from temp tables