Reload data with renamed fields

[jessica_webb]

I have a table as follows, created through many joins of different data sources:

ID	Set	Target	Current	Type	Value	Expected
68746	A	32	75	Target	32	65
68746	A	32	75	Current	75	65
2698	A	45	34	Target	45	12
2698	A	45	34	Current	34	12
968764	B	98	14	Target	98	32
968764	B	98	14	Current	14	32
21348	C	12	88	Target	12	78
21348	C	12	88	Current	88	78
9876	B	35	35	Target	35	61
9876	B	35	35	Current	35	61

What I would like now, is to keep the 'Expected' field, but reload it and add the data to the 'Type' and 'Value' fields

So for each ID and Set, I would have a third row with Type as 'Expected' and the corresponding value. Example:

ID	Set	Target	Current	Type	Value	Expected
68746	A	32	75	Target	32	65
68746	A	32	75	Current	75	65
68746	A	32	75	Expected	65	65

The 'Expected' column comes from a Left Join:

Left Join (TABLE)

IntervalMatch(Points, Set)

LOAD Lower_points,

  Expected,

  Set

Resident EXPECTED_TABLE;

So I tried adding:

Left Join (TABLE)

IntervalMatch(Points, Set)

LOAD Lower_points,

  Expected,

  Set,

  'Expected' as Type,

  Expected as Value

Resident EXPECTED_TABLE;

But that did nothing! I didn't get any errors, but also, my data didn't change!

[jessica_webb · Accepted solution]

Finally solved this with:

temp1:

CrossTable(Type, Value, 4)

LOAD

Data_point,

ID,

Set,

Subset,

Current,

Target

Expected

Resident TABLE;

LEFT JOIN (TABLE) LOAD *

Resident temp1;

Drop TABLE temp1;