Seperate two id's issue

[Nagaraj6]

Hello Guys,

I need small help

INVOICE AND REFUND	INVOICE	REFUND
1002694233	1002694233
1002694205	1002694205
1002694186	1002694186
1002689981, 20426591	1002689981	20426591
1002651796	1002651796
1002693420	1002693420
1002653261	1002653261
20427819	-	20427819
	1002693322

I have field  INVOICE AND REFUND  I need to separate INVOICE and REFUND in straight table in INVOICE AND REFUND  field Start with '10' INVOICE ,'20' REFUND .COLUMN 2 and COLUMN 3 my Expected Output.

INVOICE:=if(WildMatch(Column(1),'10*'),Column(1))

REFUND:=if(WildMatch(Column(1),'20*'),Column(1))

But I am facing issue in this  field  INVOICE AND REFUND  getting two values 1002689981, 20426591 How to separate this??

Regards,

Nag

[BrunPierre]

Hi, I'd do it at the back end.

LOAD [INVOICE AND REFUND],
If(SubStringCount([INVOICE AND REFUND],',')=1, SubField(PurgeChar([INVOICE AND REFUND],Chr(32)),',',1),
If(SubStringCount([INVOICE AND REFUND],',')=0 and WildMatch([INVOICE AND REFUND],'10*'), [INVOICE AND REFUND])) as INVOICE,
If(SubStringCount([INVOICE AND REFUND],',')=1 , SubField(PurgeChar([INVOICE AND REFUND],Chr(32)),',',2),
If(SubStringCount([INVOICE AND REFUND],',')=0 and WildMatch([INVOICE AND REFUND],'20*'), [INVOICE AND REFUND])) as REFUND
FROM Source;