How to find out the number of times the date in the string delimiter "|" or "&"

[kishoreravi1983]

One of the columns in a file coming below and we need to find out the number of date on the string to find out how many commits were done.

Is there any function in qlik sense to find out the number date in the string where delimiter "|" or "&" of the date string?

The string is given below:

Oct 4, 2018|Michael Langevin|2018-01-22 16:58:28&Sep 29, 2018|Michael Langevin|2018-01-15 03:32:07&Sep 29, 2018|Michael Langevin|2018-01-03 19:17:08&Jul 14, 2018|Brian Boyd|2017-11-17 12:39:00&Jul 9, 2018|Michael Langevin|2017-11-02 17:21:48&Jul 4, 2018|Michael Langevin|2017-09-22 16:21:04&Aug 18, 2018|Michael Langevin|2017-09-14 22:31:10&Jul 12, 2018|Michael Langevin|2017-08-22 03:31:03&Jul 2, 2018|Michael Langevin|2017-08-21 05:21:53&Feb 16, 2018|Michael Langevin|2017-08-09 17:03:02&Feb 16, 2018|Michael Langevin|2017-07-26 16:22:55

Any pointers would of great help.

Thanks,

Ravi

[sunny_talwar]

Is there any function in qlik sense to find out the number date in the string where delimiter "|" or "&" of the date string?

What does this mean? string where delimited "|" or "&" of the date string? can you explain this?

[m_woolf]

substringcount

[Gysbert_Wassenaar · Accepted solution]

Perhaps something like this:

MyTable:

LOAD

     RecordNumber,

     SubRecordNumber,

     Date(Date#(Subfield(SubRecord, '|',1),'MM D YYYY'),'YYYY-MM-DD') as Date1,

     Subfield(SubRecord, '|',2) as Name,

     Date(Date#(Subfield(SubRecord, '|',3),'YYYY-MM-DD'),'YYYY-MM-DD') as Date2,

     ;

LOAD

     RecNo() as RecordNumber,

     RowNo() as SubRecordNumber,

     SubField(MyTextString, '&') as SubRecord

FROM

     ...source

     ;

You should then be able to count the number of Date1 and Date2 values per Name value.

[kishoreravi1983]

Hi Sunny, I you see my string is separated by '|' (Pipe) and '&'.

I want to calculate how many times the date has occurred in the string

[m_woolf]

Can't you just do substringcount(YourField,'&') +1