Using subfield over two fields which are having csv values , but getting wrong results

[anu]

Hi ,

I am facing a problem while using a subfield function to split a csv value into multiple records. But I'm getting wrong results when I'm using it over two fields. When I use subfield for one field it works fine. For eg the table looks like

Load * Inline [
S_No,id_1,id_2
2,'a,b,c','a.1,b.1,c.1'
1,'x,y,z','x.1,y.1,z.1'
3,'d',dd
4,'d',dd
];

Here , 'a,b,c' of id_1 is related to 'a.1,b.1,c.1' of id_2 in sequence like 

S_No  id_1   id_2

1           a          a.1

1           b          b.1

1           c           c.1

........

I need results like above 

but getting results as

S_No  id_1   id_2

1           a          a.1

1           a         b.1

1           a          c.1

1           b         a.1

1           b         b.1

1           b          c.1

1           c         a.1

1           c         b.1

1           c           c.1

...

Please help me resolving this issue.

@jagan  @sunny_talwar @tresesco @Anil_Babu_Samineni @OlegSky 

[tresesco · Accepted solution]

Probably you are using subfield() without the third parameter. Try using it in combination with iterno() like:

Load 
	S_No,
	Subfield(id_1, ',', IterNo()) as id_1, 
	Subfield(id_2, ',',IterNo()) as id_2
	
While IterNo()<= SubStringCount(id_1, ',')+1;	

Load * Inline [
S_No,id_1,id_2
1,'a,b,c','a.1,b.1,c.1'
2,'x,y,z','x.1,y.1,z.1'
3,'d',dd
4,'d',dd
];

[anu · Accepted solution]

Thanks a lot @tresesco , it worked .