Join on a pattern

[sujit_nath]

I have a table A such as :

ID
[22][12][11]
[45]
[77][23]
[56][78]
[10][11]
[2][19]
[22]

and table B such as:

ID	value
22	Y
12	N
45	N
77	N
56	N
78	N
10	Y
2	Y
19	N
23	N
33	Y
11	Y

Now I want to take the feild ID as key and add the concatenated value to the table(left join).

-Also table A may have shared values as mentioned and table B has single values.

The resultant table will be like:

ID	value
[22][12][11]	NNY
[45]	N
[77][23]	NN
[56][78]	NN
[10][11]	YY
[2][19]	YN
[22]	Y

This would match the ID's and concatenate the value column and add it to table A.

Hope the question is clear.

Thanks and Regards,

Sujit!!

[andrey_krylov · Accepted solution]

Maybe this

[Table1]: LOAD ID, PurgeChar(SubField(ID, ']['), '[]') as ID_2

Inline "

ID

[22][12][11]

[45]

[77][23]

[56][78]

[10][11]

[2][19]

[22]

";

Left Join  LOAD ID as ID_2, value Inline "

ID, value

22, Y

12, N

45, N

77, N

56, N

78, N

10, Y

2, Y

19, N

23, N

33, Y

11, Y

"

;

[Table2]: LOAD ID, Concat(value) as value Resident [Table1] Where Len(ID) > 0 Group By ID ;

DROP Table [Table1];