Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
BI_Dev
Creator II
Creator II

Add value based on another field

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

 

 

Labels (4)
1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

 

maxgro_0-1690906736253.png

 

View solution in original post

3 Replies
maxgro
MVP
MVP

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;

 

maxgro_0-1690906736253.png

 

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!
BI_Dev
Creator II
Creator II
Author

Awesome.Thank you!