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

Looping through 2 input files

steve87
7 - Meteor

Hello,

 

i have 2 input files

TABLE1

USED_DATEPARENT_DEVICE_ID
5/17/2018 13:09:48+00:00ID1
5/20/2018 15:09:48+00:00ID2
5/17/2018 11:09:48+00:00ID3
5/14/2018 19:09:48+00:00ID1
5/22/2018 18:09:48+00:00ID2
5/17/2018 14:09:48+00:00ID1

 

TABLE2

CHILD_DEVICE_IDPARENT_DEVICE_ID
ID1_1ID1
ID1_2ID1
ID1_3ID1
ID1_4ID1
ID2_1ID2
ID2_2ID2
ID3_1ID3
ID3_2ID3

 

i would like to create loop which can check if each CHILD_DEVICE_ID from TABLE 2 is used more than once in TABLE 1 in the previous 7 days. Final results should look like this

USED_DATEPARENT_DEVICE_IDprev_7_days_used_flag
5/17/2018 13:09:48+00:00ID11
5/20/2018 15:09:48+00:00ID21
5/17/2018 11:09:48+00:00ID30
5/14/2018 19:09:48+00:00ID11
5/22/2018 18:09:48+00:00ID21
5/17/2018 14:09:48+00:00ID11

 

Is it possible to do something like this in alteryx? Thank you

6 REPLIES 6
kgalbert
9 - Comet

Hi Steve87,

 

I cannot tell when a Child Device is used based on your sample data?  Did you mean to have the Child Device ID field in Table 1?

 

Thanks,
Ken

RogerS
Alteryx
Alteryx

Loops can be done with an iterative macro but to replicate your results using a  Summarize,  formula and join tool.  I uploaded an example workflow.

steve87
7 - Meteor

Hi kbalber,

 

sorry, you are right i didn't include everything in the data set. Here is the edited version

 

 

TABLE1

USED_DATEPARENT_DEVICE_ID
CHILD_DEVICE_ID
5/17/2018 13:09:48+00:00ID1 ID1_4
5/20/2018 15:09:48+00:00ID2 ID2_1
5/17/2018 11:09:48+00:00ID3 ID3_2
5/14/2018 19:09:48+00:00ID1 ID1_2
5/22/2018 18:09:48+00:00ID2 ID2_2
5/17/2018 14:09:48+00:00ID1 ID1_3

 

TABLE2

CHILD_DEVICE_IDPARENT_DEVICE_ID
ID1_1ID1
ID1_2ID1
ID1_3ID1
ID1_4ID1
ID2_1ID2
ID2_2ID2
ID3_1ID3
ID3_2ID3

 

Table 1 Output

USED_DATEPARENT_DEVICE_ID
CHILD_DEVICE_ID
prev_7_days_used_flag
5/17/2018 13:09:48+00:00ID1 ID1_41
5/20/2018 15:09:48+00:00ID2 ID2_10
5/17/2018 11:09:48+00:00ID3 ID3_20
5/14/2018 19:09:48+00:00ID1 ID1_20
5/22/2018 18:09:48+00:00ID2 ID2_21
5/17/2018 14:09:48+00:00ID1 ID1_31

 

I would like to check if child_id from Table1 is used more then once in previous 7 days. If not then it should check second child id from Table 2 if it was used in previous 7 days in Table1. It should be checked for all child ids with the same parrent ID.

 

I hope i manage to explain it now :)

 

steve87
7 - Meteor

I just realized that i can actually just use Parent id from table 1 to calculate used count.

 

The only problem for me now is how can i check previous 7 days not just the count. @ 

 

I attached the workflow

kgalbert
9 - Comet

Hey Steve,

 

Please check out my attached solution.

 

The one you posted is pretty close to what I was doing.  Also I changed your data so there would be a child that was used more than once.  Here are the main steps:

 

  1. Formula tool to calculate if the used date is 7 days from today (I hard coded a date closer to the dates you were using.  Replace this with DATETIMENOW().  This field puts a 1 if it's within 7 days and otherwise a 0.
  2. Summarize on child device ID and sum up the 7 day flag.  Child devices with more than one use in the past 7 days will have a value greater than 1
  3. Join the summarize tool back to the formula tool and union the inner and right sides.
  4. Another formula tool to replace values greater than 1 with a 1 and other values to a 0.
  5. Select tool to drop the fields we don't need.

 

Let me know if you need any more help with this.

 

Thanks,

Ken

steve87
7 - Meteor

I manage to do it differently. 

 

First i created previous 7 days used flag and then i summarized it and created running  total to get count of previous 7 days usage 

Thank you all you helped me a lot to get to this solution
image.png

Labels