Split multiple dates from one cell into separate rows/records- Qlik Sesnse

[#1]

Hi,

is there any way to split multiple dates from each cell and produce corresponding records.

E.g in row given below I want one date in one single row. This is very critical for me. Please Help

49

$10,634.96

2016/09/17: 0.5 hrs Total: 0.5 hrs

2016/09/29: 1 hrs Total: 1 hrs

2016/09/28: 2 hrs Total: 2 hrs

2016/04/22: 0.5 hrs 2016/06/20: 0.5 hrs 2016/10/11: 0.5 hrs Total: 1.5 hrs

Thanks

[Anil_Babu_Samineni]

Could be there? Can you provide more information related the data

[#3]

sample is given below

Raw Data(all in one row)

2016/09/28: 0.5 hrs Total: 0.5 hrs

2016/09/28: 1 hrs Total: 1 hrs

2016/09/28: 1.75 hrs 2016/10/28: 1hrs Total: 1.75 hrs

2016/05/19: 0.5 hrs 2016/08/16: 0.5 hrs 2016/11/23: 0.5 hrs Total: 1.5 hrs

Loaded in QlikSense Should like (in separate rows)

Name	Date	Hrs	Date	Hrs	Date	Hrs		Hrs
Name1	9/28/2016	0.5	9/28/2016	1	9/28/2016	1.75	2016/05/19	0.5
Name1					10/28/2016	2	8/16/2016	0.5
Name1							11/23/2016	0.5

thanks

[kamal_sanguri]

use subfield function:

SubField( F,chr(10)) as NewInvoice

Replace F with your Field Name which you want to split and chr(10) with your delimiter. Pls refer attached.

[MarcoWedel]

Hi,

one solution might be:

tabTemp:
CrossTable (Col, String)
LOAD RecNo() as Name, *
FROM [https://community.qlik.com/thread/263794] (html, codepage is 1252, no labels, table is @2);

table1:
LOAD *,
    AutoNumber(RowNo(),Name&'/'&Group) as DateNo
Where Date;
LOAD Name,
    Group,
    Trim(SubField(String,':',1)) as Date,
    SubField(String,':',2) as Hrs;
LOAD Name,
    PurgeChar(Col,'@') as Group,
    SubField(String,'hrs') as String
Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco

[MarcoWedel]

or using Qlik Sense:

regards

Marco

[#7]

Thanks but its not gonna working. still on it

[#8]

HI I am unable to attach file here. can I send u my subset of data otherwise.

[Anil_Babu_Samineni]

That may offer you better. But marco gave simple and achievable solution. Why it's not going to work would you describe more. Please share sample data

[#10]

I have around 60 columns and each one having variable number of  dates with new line delimited something like

give blow as one cell. I need every date single row and every 0.5 (without hrs) single column.

2016/04/22: 0.5 hrs
2016/06/20: 0.5 hrs
2016/10/11: 0.5 hrs
Total: 1.5 hrs