Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Cross join and join on nearest date or

Highlighted
7 - Meteor

I have data with ID, DateTime, and value (step 1).

What I need to do is to find max and min date (for the whole dataset) and create a row for every 5 minutes (I have done it). (step 2)

 

Now I need to cross join created dates with ID, but I cannot do it using append fields, because I have more than 16 values. (step 3)

 

After this, I want to join data from step 1 with data from step 3 using the ID and the nearest date. Data from step 1 should be joined to data from step 3.

 

In other words.

 

In step 1 I have this table 

IDdateValue
110.02.2020 00:00321
110.02.2020 00:097121
110.02.2020 00:129127
110.02.2020 00:399581
110.02.2020 00:517683

 

And I would like to achieve this one.

IDdateValue
110.02.2020 00:00321
110.02.2020 00:057121
110.02.2020 00:109127
110.02.2020 00:159127
110.02.2020 00:209127
110.02.2020 00:259127
110.02.2020 00:309581
110.02.2020 00:359581
110.02.2020 00:409581
110.02.2020 00:457683
110.02.2020 00:507683

 

For 00:00 from the final table the nearest date is 00:00 from step 1

For 00:05 from the final table the nearest date is 00:09 from step 1

For 00:10 from the final table the nearest date is 00:12 from step 1

For 00:15 from the final table the nearest date is 00:12 from step 1

For 00:20 from the final table the nearest date is 00:12 from step 1

For 00:25 from the final table the nearest date is 00:12 from step 1

For 00:30 from the final table the nearest date is 00:39 from step 1

For 00:35 from the final table the nearest date is 00:39 from step 1

For 00:40 from the final table the nearest date is 00:39 from step 1

For 00:45 from the final table the nearest date is 00:50 from step 1

For 00:50 from the final table the nearest date is 00:50 from step 1

 

If there is the same difference between higher and lower dates, we should take the lower one.

 

Greetings 

Andrzej Gabryel

 

Highlighted
7 - Meteor

What is also important, I have many more columns then 3 and all of them have to be replicated. I have shown here only 2 to explain it easier 

Labels