Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

26 Replies

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

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

Life is so rich, and we need to respect to the life !!!
Not applicable

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

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)

   

NameDateHrsDateHrsDateHrsHrs
Name19/28/20160.59/28/201619/28/20161.752016/05/19
0.5
Name110/28/201628/16/20160.5
Name111/23/20160.5

thanks

kamal_sanguri
Valued Contributor

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

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

Hi,

one solution might be:

QlikCommunity_Thread_263794_Pic1.JPG

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

or using Qlik Sense:

QlikCommunity_Thread_263794_Pic2.JPG

regards

Marco

Not applicable

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

Thanks but its not gonna working. still on it

Not applicable

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

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

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

Life is so rich, and we need to respect to the life !!!
Not applicable

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

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
Community Browser