That would be a nice addition to Calculate transform.
1 Like
In the meantime you can use Javascript string.indexOf():
I was not using JavaScript, because you mentioned in your first post that all solutions should be without using JavaScript transform.
Otherwise it becomes easy and less transformation for the previous solutions as well.
I think using Javascript is ok if it is impossible otherwise. ;0)
nasty suggestion to get the position of a string in a string:
use split column and use the searched string as delimiter. Then you can calculate the length of the first resulting column. This plus 1 is the start position of the string within the longer string.
Good to have you join us, here is the string in question
467..114..
...*......
..35..633.
......#...
617*......
.....+.58.
..592.....
......755.
...$.*....
.664.598..
Can you get the position for the numbers and symbols #*+$ ? without using JavaScript transform.
To read more on it, here is the link to Day 3 problem.
I think @Olaf’s impressively nasty hack works.
nasty-indexof.transform (3.5 KB)
You can use a regex for the split:
But to call it inelegant would be something of an understatement! 
Ps/ Index is 0 based.
1 Like
IndexOf and LastIndexOf will be available in Calculate in the next release.
2 Likes
indexof and lastindexof are now available. Please see:
1 Like
I was away and was not using the net, so came at right time 
. If it is not bother can I trouble you to add Quarter as well in the Calculate Transform or in the DateTime format transform, which ever place you feel appropriate.
As I got some files in which I need to count the transactions Quarter wise. I could do that simply by taking the files to SQLite and do as I please over there, but I would rather stay in EDT if possible.
Will now go and download the snapshot and try to solve Day 3 problem, issue with that problem is that it needs to see the data one record above and one record below in addition to the same record as well and EDT only works with one record at a time and have no clue of record before and after.
You can calculate quarter using Calculate with operation month, then divide by 3 then Celing.
quarter.transform (3.1 KB)
I’m not sure it is worth adding another operation. I will think about it.
Ok then you can add an option in the DateTime Transform as it is related to Date? as I need to get Quarter out of the date and could use DateTime format to change to Quarter from the transaction date or use it like for example
11-Dec-2023 >>> 2023-Q4 by having format set to YYYY-Q something like that.
Is it possible to change it from zero based index to one, cause the length gives you value not zero based, so getting the index zero based is confusing (at least for me) and difficult for non programmers users.
We use a third party library for DateTime Format and it doesn’t support quarter.
I had wondered about whether it should 1 or 0 based. The Excel equivalent FIND is 1 based. The Javascript IndexOf is 0 based. As a programmer I am used to 0 based.
Also I wondered whether it should be the position of Value 1 in Value 2 or vice versa.
Anyone else got an opinion on this?
If we go logically, it should be position of Value 2 in Value 1 as I am searching the position of a substring in the first (original) string for example,
Original string = …465…
Searching string = 465
So I am looking 465 in the original string, so Value 1 is the source string and Value 2 is the substring that I am looking for.
If need to calculate Quarter myself, then I rather use JavaScript transform, because to get Quarter out of a date one need to use 3 Calculate transform and here we are always striving to go for less number of transform.
Transform file.
quarter.transform (3.6 KB)
1 Like
Idea, why not ask that third party to add quarter too?
I’m not sure either is more logical than the other. I had it that way round originally and then swapped the order to better match the Excel equivalent (FIND()).
Well i was going from the perspective left to right, you put source first and then what you want to do with that source. Anyway it is not that much trouble, but if you can make it one based instead of zero, that will be great.
Speaking personally, I’d rather see similarity with Excel in how things work. I’d expect most Easy Data Transform users will be familiar with Excel. And Apple’s Numbers FIND command also uses a 1 basis (and incidentally orders things as the position of Value 1 in Value 2).
So for me, having indexof and lastindexof as 1 based seems more logical.
And I prefer that (in similarly staying close to Excel) we search for the position of Value 1 in Value 2.
2 Likes