To Insert Dummy records
Hi Guys,
I need to insert N number of dummy records, based on the difference between two dates.
Example: My Reporting Date is Jan01,2021 and my Paid off Date is Dec01,2019, i want to find the difference between these two dates and insert records for each of the months(From Jan01,2020 till Dec01,2020).
How i can do in Datameer?
Sorry if my Grammar is poor.
-
Hello,
I am pasting the sample excel with results i am looking far. Here in the first table i identified one record with paid off date on Jan01 2021, after that month this record was not getting added in our base, bcoz its already got paid off, but now we got one request to add those records, so just like in the second table, i need to add the dummy records from Feb2021 till Sep2021 along with MOB that also needs to be Incremental. We identified the MOB for that Paid off account was 25 on Jan 2021, so if i am adding dummy records till Sep 2021, i want MOB also to be Incremental. Hope this will help you to understand.
Thanks !
-
Hello Chandra.
Here is the method I could offer.- Convert ReportingDate and PaidOffDate columns from string to date using the formula ASDATE(#<ColumnName>; "dd-MMM-yyyy").
- Find the difference between these two dates in months via TIMESTAMPDIFF("MONTH"; #PaidOffDate; #ReportingDate).
- Generate dummy records which number equals the time difference in months via EXPLODE_RANGE(0; #TimeDiffInMonths).
- Make MOB values incremental within the group by summing original MOB and ExplodeRande columns.
Here is the result I've got using the above approach.
If this method brings an extra record because EXPLODE_RANGE(0; N) formula creates N+1 records, you could workaround this condition by subtracting 1 from the TimeDiffInMonths column.
-
Hello - Thank you so much for the above offer its working fine, but i want to add one more Col called New Reporting Date that should contain the each months. In the above Example for Jan1,2021 Paid Off account we got 8 dummy records now, along with that i need that new reporting col that should contain each months like Jan1, 2021, Feb1, 2021, Mar1, 2021 till Sep1,2021, so that i can take this to append in my base, based on that New Reporting Date.
-
Hello Chandra.You probably could:
- Get the month number from the PaidOffDate value via MONTH(#PaidOffDate)
- Sum up #PaidOffDateMonth and #ExplodeRange. As ExplodeRange value is incremental within a group, summing it up with PaidOffDateMonth gives you a month range between PaidOffDate and ReportingDate.
- As the last step, you would need to build a string from
- a) ReportingDate day
- b)NewReportingDateMonth
- c) ReportingDate year
and convert this string into a date via ASDATE( T(DAY(#ReportingDate)) + "-" + T(#NewReportingDateMonth) + "-" + T( YEAR(#ReportingDate)); "dd-MM-yyyy")
I'm not sure, though, if this approach would work if ReportingDate and PaidOffDate are from different years.
Please sign in to leave a comment.
Comments
7 comments