Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please help with getting a row to be created .
in the final table, I need to insert Need_ids from both the tables
here is the sample data
ID_NAME Table:
Need_ID field_name
1 n1
2 n2
3 n3
4 n4
5 n5
6 n6
7 n7
8 n8
9 n9
10 n10
11 n11
12 n12
13 n13
14 n14
15 n15
Table1:
Need_ID state_ID country_id value
1 s1 c1 abc
2 s1 c1 def
3 s1 c1 ghi
4 s1 c1 jkl
5 s1 c1 mno
1 s3 c1 abc
2 s3 c1 def
13 s3 c1 bnm
1 s2 c2 qqq
2 s2 c2 sss
3 s2 c2 ggg
3 s4 c2 lorp
14 s4 c2 nevs
2 s5 c2 sss
12 s5 c2 uicvb
14 s5 c2 8feiru9u9
Table2:
Need_ID country_id value
1 c1 abc
2 c1 def
5 c1 mno
6 c1 pqr
7 c1 stu
8 c1 vwx
2 c2 sss
5 c2 ioio
Need final table data as below
Final table:
Need_ID state_id country_id value field_name
1 s1 c1 abc n1
2 s1 c1 def n2
3 s1 c1 ghi n3
4 s1 c1 jkl n4
5 s1 c1 mno n5
6 s1 c1 pqr n6
7 s1 c1 stu n7
8 s1 c1 vwx n8
1 s2 c2 qqq n1
2 s2 c2 sss n2
3 s2 c2 ggg n3
5 s2 c2 ioio n5
1 s3 c1 abc n1
2 s3 c1 def n2
13 s3 c1 bnm n13
5 s3 c1 mno n5
6 s3 c1 pqr n6
7 s3 c1 stu n7
8 s3 c1 vwx n8
3 s4 c2 lorp n3
14 s4 c2 nevs n14
2 s4 c2 sss n2
5 s4 c2 ioio n5
2 s5 c2 sss n2
12 s5 c2 uicvb n12
14 s5 c2 8feiru9u9 n14
5 s5 c2 ioio n5
Try this
Table1:
LOAD * INLINE [
Need_ID, state_ID, country_id, value
1, s1, c1, abc
2, s1, c1, def
3, s1, c1, ghi
4, s1, c1, jkl
5, s1, c1, mno
1, s3, c1, abc
2, s3, c1, def
13, s3, c1, bnm
1, s2, c2, qqq
2, s2, c2, sss
3, s2, c2, ggg
3, s4, c2, lorp
14, s4, c2, nevs
2, s5, c2, sss
12, s5, c2, uicvb
14, s5, c2, 8feiru9u9
];
Table2:
LOAD * INLINE [
Need_ID, country_id, value
1, c1, abc
2, c1, def
5, c1, mno
6, c1, pqr
7, c1, stu
8, c1, vwx
2, c2, sss
5, c2, ioio
];
Left Join (Table2)
LOAD Distinct country_id,
state_ID
Resident Table1;
Concatenate(Table1)
LOAD *
Resident Table2;
DROP Table Table2;
So, we are only filling in state_id here? but this seems like we are looking at only one country_id with one state_id... it might help if you share some more rows of data with multiple country_ids and state_ids with the expected output to show the general idea.
Try this
Table1:
LOAD * INLINE [
Need_ID, state_ID, country_id, value
1, s1, c1, abc
2, s1, c1, def
3, s1, c1, ghi
4, s1, c1, jkl
5, s1, c1, mno
1, s3, c1, abc
2, s3, c1, def
13, s3, c1, bnm
1, s2, c2, qqq
2, s2, c2, sss
3, s2, c2, ggg
3, s4, c2, lorp
14, s4, c2, nevs
2, s5, c2, sss
12, s5, c2, uicvb
14, s5, c2, 8feiru9u9
];
Table2:
LOAD * INLINE [
Need_ID, country_id, value
1, c1, abc
2, c1, def
5, c1, mno
6, c1, pqr
7, c1, stu
8, c1, vwx
2, c2, sss
5, c2, ioio
];
Left Join (Table2)
LOAD Distinct country_id,
state_ID
Resident Table1;
Concatenate(Table1)
LOAD *
Resident Table2;
DROP Table Table2;
Thanks Sunny, this is working... but it is taking very long time. Probably because of Left join.
Can you please suggest on this.
Don't know/think if there is way to do this without a join....
Even apply map is taking long time.
I don't know how you are using ApplyMap in this situation... can you show me the applymap based solution you have tried?