Add value based on another field

[BI_Dev]

Hi - I have table as below.

ID	IDP	Value	Value_New
1		ABC	ABC
2		XYZ	XYZ
	1		ABC

I want to create new column as above column 'Value_New', where value gets added based on ID match.

In the above table ID column has '1' and IDP has '1'  and I want the Value_New to be filled in based on ID match and Value column. So in the above Example 'ABC' gets added to Value_New column.IS this doable ?

@sunindia

[maxgro · Accepted solution]

try with

// your data 

T:
load * inline [
ID, IDP, Value
1, , ABC
2, , XYZ
, 1,
];

// from ID to Value
Map_ID_to_Value:
Mapping load ID, Value Resident T WHERE LEN(TRIM(ID))>0;

T2:
LOAD
*,
ApplyMap('Map_ID_to_Value', ID, ApplyMap('Map_ID_to_Value', IDP)) as Value_New
Resident T;

DROP TABLE T;

[Aurelien_Martinez]

Hi,

In the script you can try a mapping table :

_map:
Mapping Load
  ID,
  Value
From table
;

Load
  ID,
  IDP,
  Value,
  ApplyMap('_map', IDP, 'N/A') as Value_New
From table
;

Aurélien

[BI_Dev]

Awesome.Thank you!