adding additional missing field values to existing dataset

[RogerG]

if I have a table such as

Table1:

load * inline [

ID,  alpha, value

1, a, 3

1, b, 5

1, c, 5

2, a, 7

2, d, 8

]

then wanted to add additional fields to it, from a "Master" list of values

Master:

load * inline [

Value

a

b

c

d

]

Now I need to make the final table  the values of table 1 with the blanks populated for each ID with the missing ID and letter combination

ID,  alpha, value

1, a, 3

1, b, 5

1, c, 5

1,d

2, a, 7

2, b

2, c

2, d, 8

[MayilVahanan · Accepted solution]

Hi @RogerG 

Try like below

Table1:

load * inline [

ID, alpha, value

1, a, 3

1, b, 5

1, c, 5

2, a, 7

2, d, 8

];

Temp:
Load Distinct ID Resident Table1;
Master:
Join
load Value as alpha inline [
Value
a
b
c
d
];
Join(Table1)
Load * Resident Temp;

DROP Table Temp;

[Qlik1_User1]

@RogerG , Try this.

Table1:

LOAD ID;
load * inline [

ID, alpha, value

1, a, 3

1, b, 5

1, c, 5

2, a, 7

2, d, 8

];

JOIN(Table1)

load * inline [

Value

a

b

c

d

];

NoConcatenate
Master1:
LOAD ID,Value as alpha Resident Table1;
DROP Table Table1;
JOIN (Master1)
LOAD ID, alpha, value;
load * inline [

ID, alpha, value

1, a, 3

1, b, 5

1, c, 5

2, a, 7

2, d, 8

];

NoConcatenate
Final:

LOAD ID,alpha,value Resident Master1;
DROP Table Master1;