Split comma separated values into rows

[Varun1]

Hi,

I have the following data in excel.

Update Date	Update Description
Mon Mar 16 00:00:00 IST 2020; Tue Jun 09 00:00:00 IST 2020; Wed Aug 19 00:00:00 IST 2020; Wed Oct 07 00:00:00 IST 2020;	filed vakalatnama  on said date;"submitted written reply on June 9~ 2020";Matter postponed and next date of hearing given by court ;Matter postponed and next date of hearing given by court ;

I want to load above as below mentioned table structure in data model.

Update Date	Update Description
Mon Mar 16 00:00:00 IST 2020	filed vakalatnama  on said date
Tue Jun 09 00:00:00 IST 2020	submitted written reply on June 9~ 2020
Wed Aug 19 00:00:00 IST 2020	Matter postponed and next date of hearing given by court
Wed Oct 07 00:00:00 IST 2020	Matter postponed and next date of hearing given by court

Please advice as i am in need of this at the earliest.

[Ivan_Bozov]

Try SubField: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Strin...

[Varun1]

Hi Ivan,

I have already tried using the SubField function but i do not get the desired out with it. I have attached the desired output and the output which I got using the Subfield function. Please advice if there is a way to get the desired output.

Thanks 

[brunobertels · Accepted solution]

Hi 

Try this 

[TempTable]:
LOAD * INLINE
[
Update Date,Update Description
Mon Mar 16 00:00:00 IST 2020; Tue Jun 09 00:00:00 IST 2020; Wed Aug 19 00:00:00 IST 2020; Wed Oct 07 00:00:00 IST 2020; ,filed vakalatnama on said date;"submitted written reply on June 9~ 2020";Matter postponed and next date of hearing given by court ;Matter postponed and next date of hearing given by court ;
](delimiter is ',');

Table1:
NoConcatenate
Load
rowno() as Key,
subfield([Update Date],';') as [Update Date]

resident TempTable;

Left Join(Table1)

Table2:

Load
rowno() as Key,
subfield([Update Description],';') as [Update Description]
resident TempTable;

Drop table [TempTable];

You may have this as output 

[Varun1]

Hey Bruno,

Thank you so much!!!

This works. 😁👍