Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
anu
Partner - Contributor III
Partner - Contributor III

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

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 

2 Solutions

Accepted Solutions
tresesco
MVP
MVP

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
];

 

11.PNG

  

View solution in original post

anu
Partner - Contributor III
Partner - Contributor III
Author

Thanks a lot @tresesco , it worked .

View solution in original post

2 Replies
tresesco
MVP
MVP

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
];

 

11.PNG

  

anu
Partner - Contributor III
Partner - Contributor III
Author

Thanks a lot @tresesco , it worked .