Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Compare tables without joining data

df
8 - Asteroid

Hello,

 

I have two tables which look like here:

 

1)

DateCount
2018-11-0145
2018-11-0233
2018-11-0337
2018-11-0434
2018-11-0541

 

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

 

9 REPLIES 9
ponraj
13 - Pulsar

can you also post sample output you are looking for ?

 

df
8 - Asteroid

Hi @ponraj,

 

yes.

 

It should look like here:

 

EventdateCountCount_day_before
2018-11-023345
2018-11-043437
2018-11-043437

 

Thanks

ponraj
13 - Pulsar

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

 

workflowworkflowResultsResults

SamDesk
11 - Bolide

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.

Capture.PNG

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
8 - Asteroid

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
11 - Bolide

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
8 - Asteroid

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

 

Eventdatedays0days1days2days3days4days5...days29days30
2018-11-023345???? ??
2018-11-0434373345?? ??
2018-11-0434373345?? ??

 

And i would appreciate a way without 30 tools :)

Just wondering if there is such a way.

SamDesk
11 - Bolide

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.

Capture.PNG

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

Capture1.PNG

 

Hope that achieves what you're looking for.

 

Sam :)

 

df
8 - Asteroid

@SamDesk

Yes thats exactly what i am looking for.

Thank you very much :)

 

 

Labels