Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kishoreravi1983
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
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
Master II
Master II

substringcount

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

kishoreravi1983
Contributor III
Contributor III
Author

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
Master II
Master II

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