Compare and insert between dates

[Pouchy]

Hi everyone,

I have this :

And this :

And I want exactly this (I made it by hand in excel) :

Don't worry about the currency column, I don't care about it.

What I want is to compare the second table line by line to the first one.

999991 is my opening and 999994 is my closing.

I want to compare the dates and put the line from the second table to the first one.

For example, I have a few rows in the second table for the 2019-02-20.

By comparing the date, i want to insert them between my opening and closing at date 2019-02-24 (Picture 3)

Is it possible?

[afv2688]

You can use a union tool which will put both of your tables together. Then use the sort tool to sort by date and after a select too to reorder all the columns as you like

[Pouchy]

Thanks afv2688, but the thing is that, if you see picture 3, the dates are not in order. 

The 20-02-2019 are insert between the 24-02-2019 (999991) and 24-02-2019 (999994)

I can't just sort them because I need them to be in this order.

[afv2688]

Without knowing the exact rule you are applying cant help you much further.

Another way you could do it is to use the filter tool to separate the data by the dates you are asking (lower than, between or greater than and then use multiple union tools to "glue" the data as you want, given that the union tool always sets the data by the order you set the input (1st comes top of the table, 2nd next, etc)

The thing is that this is a very manual way and with too many records can be exhausting

[yalmar_m]

HI @Pouchy,

I think you want to union both tables and sort the data based on an Indicator.

Can you provide me with a (sample of) your data so I can build a solution?

 Best,
Yalmar

[Pouchy]

Hello,

Here is the data.

The first table contain the data and the second is where I want to insert them.

Just for information, in the second table, 999991 is the opening and 999994 is the closing.

What I mean by this is that every date correspond to a week.

For example, in the second table, the 03-02-19 is a week from 01-02 to 03-03. 

Same goes for 10-02-19. it correspond to the 04-02 to the 10-02.

[danilang · Accepted solution]

Hi @Pouchy 

This was a tricky one worthy of a weekly challenge. 

The key was to generate a set of ids based on the way that you want to interleave your records.  The 99999x records form the brackets for the detail records that are inserted within each bracket.  To figure out which bracket to use, I generated all dates from the end of the previous bracket to the current one and used that MatchingDate to perform my join.

Note that I used your initial pics as the basis for the data so I don't have the 99999x records after 2019-02-28

Dan

[yalmar_m]

Hi @Pouchy,

This might be a solution, would it be of any help?

@danilang I agree, a weekly challenge would be nice around this subject.

Best,

Yalmar

[Pouchy]

Thank you very much everyone ! It works well now !

Yeah, definitly a small challenge. It's been 2 two days that I'm stuck on this problem. I would never have thought doiing something like this.

Thanks again @danilang and everyone !