Getting particular value from string in a new column

[poojashribanger]

Hi,

I have one column which has value like 101,202,303 so for each ID i need to populate newcolumn using my Tran column.

condition is like i have to take value starting from '3' only.

ID	Tran
101	101,201,301,401,501,601
102	302,504
103	109,303,200
104	204,140,309
105	304,205

expected Output

ID	Tran	NewColumn
101	101,201,301,401,501,601	301
102	302,504	302
103	109,303,200	303
104	204,140,309	309
105	304,205	304

Any help would be kindly appreciated.

Thanks & Regards,

Poojashri

[kaushiknsolanki · Accepted solution]

Try this.

Data:
Load * inline [
ID; Tran
101;101,201,301,401,501,601
102;302,504
103;109,303,200
104;204,140,309
105;304,205
] (delimiter is ';');

Temp:
Load ID, Tran,SubField(Tran,',') as NewField
Resident Data ;

Drop table Data;

Final:
NoConcatenate Load * Resident Temp where WildMatch(NewField,'3*');

Drop table Temp;

[avinashelite]

You could try like this also

Load ID, Tran,

SubField(Tran,',', wild match(Tran,'3*') ) as NewField
Resident Data ;

[poojashribanger]

Thanks Kaushik, It did work for me 🙂

[kaushiknsolanki]

Good to know that @poojashribanger. 

Wish you also understood how to do such things.

[poojashribanger]

Yes, thanks i was adding the 3rd parameter because of that it was giving me wrong result.