Compare tables without joining data

Hello,

I have two tables which look like here:

Date	Count
2018-11-01	45
2018-11-02	33
2018-11-03	37
2018-11-04	34
2018-11-05	41

Eventdate

2018-11-02

2018-11-04

Lets say i wanna do this: For every Eventdate get the "Count" for the matching "Date" and the Date befor that (Row-1) [Assuming the Date Table is orderd and every Date has an entry].

So this is no Problem if the "Eventdate" isnt doubled in the table then i can just match them and use multirow formular.

But i dont want to delete a row (they differ in other columns).

Is there a Formular or Tool that can do this?
Like get Eventdate and look this up in the DateTable and get that Date Count and the Count before that Date.

Thanks

Tips and Tricks

Preparation

Accepted answers

SamDesk

Hi @df,

Certainly, thank you for the explanation. So we can achieve this by using a batch macro to iteratively change the properties of the multi-row function.

Here we have taken the original flow I provided and set it up to receive macro inputs via a control parameter and configured it to change the various properties of the multi-row function. Then in a new flow (below), we can configure a Min and Max value to generate a range of values and then pass these values to the new macro function (purple box in the flow).

Hope that achieves what you're looking for.

Sam

Compare tables without joining data.yxzp

All comments

ponraj

can you also post sample output you are looking for ?

Hi @ponraj,

yes.

It should look like here:

Eventdate	Count	Count_day_before
2018-11-02	33	45
2018-11-04	34	37
2018-11-04	34	37

Thanks

SamDesk

Hi @df,

This flow should achieve what you are looking for. It searches for the row before in the Date datasource regardless how many days previous it is.

It relies, as you say, on the Date data source being in the correct order but you could enforce this by using a Sort tool.

Sam

Compare tables without joining data.yxmd

Thank you,

But lets say i dont just want the previous day - i want like 30 days before the eventday.

@ponrajIn your solution i would need to create 29 new Columns (date1, date2, [...], date29) date1 = date before, date2 = two days before

and then use 29 jointools (for every data column on tool to match with Date/Count Table)

@SamDeskhere i need to create 29 multi row formular.

Isnt there a easier way to do this?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703