Reference Previous Rows

[mwarqee]

Expected Behavior

When generating date ranges, avoid overlapping ranges with previous rows.

Current Behavior

I have read and followed the articles here: https://databrickslabs.github.io/dbldatagen/public_docs/DATARANGES.html?highlight=previous+rows & here https://databrickslabs.github.io/dbldatagen/public_docs/reference/api/dbldatagen.column_generation_spec.html?highlight=basecolumns#dbldatagen.column_generation_spec.ColumnGenerationSpec.baseColumns

Current output:

Basically the next row values for date_start & end should be created based on the previous rows values to avoid overlaps.

Steps to Reproduce (for bugs)

Generate synthetic data for a hotel booking system with fields Hotel_name, room_number, room_status, start_date, end_date.

Context

Specific use case - generate synthetic data for hotel bookings. Logically you cannot have 2 or more rows with the same hotel_name,room_number, start_date and different or same room_status (available, booked, reserved). I am trying to find a mechanism to first check previous rows, and when assigning the room_status ensure that room_number, hotel, date_start, date_end does not overlap.

Your Environment

• dbldatagen version used:dbldatagen-0.3.4.post2
• Databricks Runtime version: '13.1.x-cpu-ml-scala2.12'
• Cloud environment used: Azure

[ronanstokes-db]

Thanks for filing this issue.

By design, the values for a given row do not depend on the previous row. This is to enable highly scalable parallelism.

However you could compute the combination of dates, hotels and room ids based on some other intermediate values to guarantee non overlap. Alternatively you could generate the data as you do in the example above and filter out conflicting data using where clauses on the data frame resulting from build.

To use the filtering approach, you would need to expand the records for date range, hotel and room id to produce one row for each day and then drop duplicates. You can then roll up the resulting data to produce the new date ranges.

I have some additional features in development regarding modelling more complex timing events - but it wont necessarily address this specific issue.

[mwarqee]

Thanks for replying... The issue is nost simply limited to double entries as in the picture above but also to overlaps:

So in the image above, because of the prior row's date values, the following row's start_date should be ((1+max(End_date)) of all the end_date values in that column (which have the same hotel_id,room_number,room_type,room_status)- which in my view should happen at generation time.
I do not know how to implement that currently with the existing features in dbdatagen. I know my example is very specific, but I do see this as being applied across various use cases.
I'll keep experimenting, to see what is possible & looking forward to what will be implemented in the next releases with respect to conditional row generation values.

[ronanstokes-db]

Do you have some code that you can share so that i can repro what you are doing ?