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

[#2]

can I share with you my original sheet. I am in trouble working with it. I could share with you in private. thanks

[MarcoWedel]

Hi,

using the sample data you posted in split Multiple dates

a possible solution could be:

tabTemp:
CrossTable (Group, String)
LOAD RecNo() as ID, *
FROM [https://community.qlik.com/servlet/JiveServlet/download/1287137-282977/Book1.xlsx] (ooxml, no labels, table is Sheet1);

table1:
LOAD *,
     AutoNumber(RowNo(),ID&'/'&Group) as DateNo
Where Date like '????/??/??';
LOAD ID,
     Group,
     Trim(SubField(String,':',1)) as Date,
     SubField(String,':',2) as Hrs;
LOAD ID,
     Group,
     SubField(String,'hrs') as String
Resident tabTemp
Where not Match(Group,'A','B','C');

DROP Table tabTemp;

hope this helps

regards

Marco

[#4]

thanks but again all dates in one cell

[MarcoWedel]

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

[#6]

I just marked positive as I am getting alert again & again. My problem is still there . I have big sheet with over 60 columns.

[Anil_Babu_Samineni]

You are not expecting that? The image looks like helpful to you. I am not sure exactly what you gonna to do. Have you got solution from Marco. If though, Please close this thread if not please response back to same thread in detail

[#8]

I think only full data set would be able to explain the scenario. I cannot share full data publically so I will be closing this thread. If I could share with you the data in private msg then do let me know. thanks