Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Fill In Existing Field

Looking for an efficient way to solve this.

Current Data:

ID, Letter, Test

01, A, 1

02, B, N/A

03, C, 2

04, D, 3

05, E, N/A


New List:

Letter, Test

B, 5

B, 6

E, 8

E, 9

OUTCOME:

01, A, 1

02, B, 5

02, B, 6

03, C, 2

04, D, 3

05, E, 8

05, E, 9

Hope this helps.

4 Replies
kaanerisen
Creator III
Creator III

Try this:

temp_current:

load * inline [

ID, Letter, Test

01, A, 1

02, B, N/A

03, C, 2

04, D, 3

05, E, N/A

];

Left Join (temp_current)

New:

load * inline [

Letter, Test2

B, 5

B, 6

E, 8

E, 9

];

current:

Load

ID,

Letter,

If(Test<>'N/A',Test,Test2) as Test

Resident temp_current;

Drop Table temp_current;

devarasu07
Master II
Master II

Hi,

Try like this.

TestMap:

Mapping Load * Inline [

Letter, Test

    B, 5

    B, 6

    E, 8

    E, 9

];

Test:

load *,ApplyMap('TestMap',Letter,Test) as TestRevised;

LOAD * INLINE [

    ID,Letter, Test

    01, A, 1

    02, B, N/A

    03, C, 2

    04, D, 3

    05, E, N/A

];

Drop Field Test from Test;

RENAME Field TestRevised to Test;

Thanks,Deva

MayilVahanan

Hi

Try like this

Current_Data:

Load * Inline

[

ID, Letter, Test

01, A, 1

02, B, N/A

03, C, 2

04, D, 3

05, E, N/A

];

New_List:

Join

LOAD Letter, Test as T1 Inline

[

Letter, Test

B, 5

B, 6

E, 8

E, 9

];

Final:

LOAD Letter, If(Test = 'N/A', T1, Test) as Test, ID Resident Current_Data;

DROP Table Current_Data;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
hemeshreddy
Partner - Contributor III
Partner - Contributor III

you can try this

CurrentData:

load * Inline [

ID, Letter, Test

01, A, 1

02, B, N/A

03, C, 2

04, D, 3

05, E, N/A ] Where Test='N/A';

left Join

Tmp_NewList:

LOAD Letter, Test as Test2;

LOAD * Inline [

Letter, Test

B, 5

B, 6

E, 8

E, 9 ];

DROP Field Test;

RENAME Field Test2 to Test;

Concatenate(CurrentData)

load * Inline [

ID, Letter, Test

01, A, 1

02, B, N/A

03, C, 2

04, D, 3

05, E, N/A ] Where Test<>'N/A';

resgards,

hemesh