Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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?
substringcount
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.
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
Can't you just do substringcount(YourField,'&') +1