Datestamp Not Copying on spliting

Question & Answer
,
1

I have two columns where the first column “start date” has concatenated dates and time stamp i.e., 23:00 hrs in attached image

I want to/used split function in the second column to extract of max of date from the first column.

Calculation I used for now

parseDate(split({Start Date},“;”,1))

Challenge: the Timestamp is 23:00 hours in start date column and new column is giving 00:00:00 time stamp, I intend to copy the same time stamp and also max of whatever date is in concatenated Startdate column.

split
split727×258 6.68 KB

2

parseDate(split({Start Date},“;”,1))

I tried max(parseDate(split({Start Date},“;”,1)))

its not working

3

Are you grouping it down to the day in your field?

image
image799×555 50.3 KB

4

Thanks for the suggestion, it worked, much appreciated. Any idea how to extract Max date after splitting?

5

In that case it seems like you’ll have to add some logic.

First I would look to see if there is a semi colon. If there is then compare in an ifelse and return the greatest one. If not, then just return the date.

ifelse(
locate({Start Date},‘;’)>0,
ifelse(parseDate(split({Start Date},“;”,1))> parseDate(split({Start Date},“;”,2)), parseDate(split({Start Date},“;”,1)),parseDate(split({Start Date},“;”,2))),
parseDate(split({Start Date},“;”,1))
)

6

Thanks, I will check and get back to you.

1 Like
7

Hi Max,

Just a slight help its showing an error, I do get your logic. Tried to resolve it but unsuccessful, can you please chime on it

ersolv
ersolv1285×319 33.6 KB

Thanks

8

Yes there is a ; semicolon between the concatenated dates

concat

9

It looks like you have an extra ) at the end of your calculated field.

ifelse(
locate({Start Date},‘;’)>0,
ifelse(parseDate(split({Start Date},“;”,1))> parseDate(split({Start Date},“;”,2)), parseDate(split({Start Date},“;”,1)),parseDate(split({Start Date},“;”,2))),
parseDate(split({Start Date},“;”,1))
)

1 Like
10

Thanks Max

I checked the locate function syntax, and the semicolon was included in single quotes, which may be why it was failing, I now used " double quotes.

I examined the brackets and they appear to be in order, but I am now experiencing a new issue in the if else line. I’m learning as I go. Please let me know if you find why the error anything. PFA.

Many thanks

ifelse err
ifelse err1219×463 37 KB

11

climage
climage1230×474 29.7 KB

12

Hmm, can you try and do this incognito mode? I had an issue that it wouldn’t save as well.

Also, you might want to add in the format of the parseDate after you take the string of the split.

13

Thanks Max, yes I have added the format it worked

1 Like
14

Max the calculation is working fine, a little guidance if you don’t mind, under same scenario, I recently discovered that there are concatenated dates more than 2, any idea how to pick the max date if there are 10 or more concatenated dates instead of just 2

15

In quicksight you would have to do an ifelse for each scenario.

I would defintley recommend moving this to SQL if you can and using array functions to get a max of the array.

Here is a trimmed down version of how it would look in a quicksight calculated field.

ifelse(
locate({Start Date},‘;’)>0,
ifelse(parseDate(split({Start Date},“;”,10))> parseDate(split({Start Date},“;”,9)) AND
parseDate(split({Start Date},“;”,10))> parseDate(split({Start Date},“;”,8)) AND
parseDate(split({Start Date},“;”,10))> parseDate(split({Start Date},“;”,7)) etc…), parseDate(split({Start Date},“;”,10)),
parseDate(split({Start Date},“;”,10))< parseDate(split({Start Date},“;”,9)) AND
parseDate(split({Start Date},“;”,10))> parseDate(split({Start Date},“;”,8)) AND
parseDate(split({Start Date},“;”,10))> parseDate(split({Start Date},“;”,7)) etc…)
parseDate(split({Start Date},“;”,9))), etc…,
parseDate(split({Start Date},“;”,1))
)

1 Like