topic Re: Split multiple dates from one cell into separate rows/records- Qlik Sesnse in QlikView

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

Tue, 13 Jun 2017 04:33:14 GMT

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

$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

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

Anil_Babu_Samineni — Tue, 13 Jun 2017 05:17:18 GMT

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

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

Tue, 13 Jun 2017 06:44:22 GMT

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

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

kamal_sanguri — Tue, 13 Jun 2017 07:40:47 GMT

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.

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

MarcoWedel — Tue, 13 Jun 2017 22:26:58 GMT

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

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

MarcoWedel — Tue, 13 Jun 2017 22:46:42 GMT

or using Qlik Sense:

regards

Marco

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

Tue, 13 Jun 2017 23:28:27 GMT

Thanks but its not gonna working. still on it

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

Tue, 13 Jun 2017 23:44:59 GMT

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

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

Anil_Babu_Samineni — Wed, 14 Jun 2017 04:25:58 GMT

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

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

Wed, 14 Jun 2017 04:40:30 GMT

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

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

Anil_Babu_Samineni — Wed, 14 Jun 2017 04:57:40 GMT

Sorry to ask you If possible, can you please share sample excel at least 10 columns. Because, i feel we can do this by using Cross table.

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

Wed, 14 Jun 2017 05:42:09 GMT

How to share file? I didn't find any option to attach file here.

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

Anil_Babu_Samineni — Wed, 14 Jun 2017 05:44:59 GMT

Uploading a Sample

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

Wed, 14 Jun 2017 06:08:20 GMT

I have to share as new thread. could u find that?

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

Anil_Babu_Samineni — Wed, 14 Jun 2017 06:42:17 GMT

Got it, split Multiple dates

Let me play around, Here, I recommend you to delete new thread and use same here. I will attach for you

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

Anil_Babu_Samineni — Wed, 14 Jun 2017 07:46:20 GMT

Can you check this?

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

Wed, 14 Jun 2017 14:23:46 GMT

what?..there is nothing to check.

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

Anil_Babu_Samineni — Wed, 14 Jun 2017 14:27:35 GMT

Hahaha, you are not getting this ?

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

Wed, 14 Jun 2017 15:33:16 GMT

Makes sense. But I wonder how to get it for over 30 columns with no data anomaly in it.

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

Wed, 14 Jun 2017 15:54:51 GMT

Hi do u have any final solution now? today is my deadline