To Insert Dummy records

Follow
chandra kumar

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.

1

Comments

7 comments

Date Votes
  • Konsta Danyliuk

    Hello Chandra.

    Would you mind providing a sample or the source data (Reporting Date, Paid off Date) and the result you are looking for? E.g., a mock-up in Excel. This helps us to understand the desired output and explain how to get it.

    1
    Comment actions Permalink
  • chandra kumar

    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 !

    1
    Comment actions Permalink
  • chandra kumar

    Hello - Just to add, once it get Paid Off country wont submit those records into the Containers, so only we need to add dummy records.

     

    1
    Comment actions Permalink
  • Konsta Danyliuk

    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.  

    2
    Comment actions Permalink
  • chandra kumar

    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.

    1
    Comment actions Permalink
  • Konsta Danyliuk
    • Edited

    Hello Chandra.You probably could:

    1. Get the month number from the PaidOffDate value via MONTH(#PaidOffDate)
    2. 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.
    3. 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.

    1
    Comment actions Permalink
  • chandra kumar

    Hello,

    Yes that might create a problem, let me think on that part, and if you got anything better to avoid this issue, that also be great, but thank you so much for all of your inputs.

    1
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post