Compare tables without joining data

[df]

Hello,

I have two tables which look like here:

1)

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

2)

Eventdate

2018-11-02

2018-11-04

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

[ponraj]

can you also post sample output you are looking for ?

[df]

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

[ponraj]

Here is the sample workflow for your case.  I used Join tool.

workflowResults

[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 :)

[df]

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?

[SamDesk]

Hi @df,

If you want 1 day before and also 30 days before then you would just need 2 multi-row formulas to produce this.

Can you provide a clearer idea of what you want your dataset to look like in totality?

Sam :)

[df]

@SamDeskSorry for the missunderstanding.

i dont want the count from 1-day-before and 30-days-before.

i want the count form the eventdate (0-day-before), 1-day-before, 2-days-before and so on till 30-days-before.

So in the end it should look like this:

Eventdate	days0	days1	days2	days3	days4	days5	...	days29	days30
2018-11-02	33	45	?	?	?	?		?	?
2018-11-04	34	37	33	45	?	?		?	?
2018-11-04	34	37	33	45	?	?		?	?

And i would appreciate a way without 30 tools :)

Just wondering if there is such a way.

[SamDesk · Accepted solution]

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 :)

[df]

@SamDesk

Yes thats exactly what i am looking for.

Thank you very much :)