Seperate two id's issue

Nagaraj6 — Wed, 11 Oct 2023 05:40:27 GMT

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

Re: Seperate two id's issue

BrunPierre — Wed, 11 Oct 2023 22:56:07 GMT

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;

topic Seperate two id's issue in Qlik Automate

Seperate two id's issue

Re: Seperate two id's issue