Forum Discussion
Work out dates between one column on multi rows
In the attached, the same query adapted to a changed column name from Date Notified to createdon.
It produces just short of 900k rows so I've reduced it to one row (to be able to attach it here) which means you'll need to refresh the query to see those rows. it takes a few seconds to generate those rows…
Hi Casi , have a look at this file and let me know if this is what you re looking for? I'll attach the file and images of the output. You can see the code from Advance Editor in the PQ interface. Thanks
https://docs.google.com/spreadsheets/d/1EKubQ_sRc0n-ka1dx6k2xS3uDz0a_c3t/edit?usp=sharing&ouid=104752674875039603034&rtpof=true&sd=true
Hi Sundar,
This is what i am wanting but how did you do it?
Thank you
Casi
Below are the steps that I've done in the adv editor. Do let me know if there are any clarifications or explanations needed. ThanksCreate an Index Column Starting from 1: The first step is to add an index column that starts from 1 instead of 0. This is necessary because you want to refer to the second row in the [DateNotified] column, which will act as the end date. So, the index will start from 1 to point to the second row
Create a List of Dates: Use the [DateNotified] and [DateChanged] columns to generate a list of dates for each row. This step creates a range of dates between the two columns.
Expand into Separate Rows: Finally, expand the list of dates into separate rows to get the desired output.
Error Handling (Try-Otherwise): Since the last row will have an index of '4' but only 3 i.e. (Row 0 to Row 3) rows exist, the process may not return any data for the last row. To prevent errors, the Try-Otherwise condition is used, ensuring that if no data is returned, the last value is used instead.
Where did you get the date changed column from please as its not in my dataset
