Hello,
i have 2 input files
TABLE1
USED_DATE | PARENT_DEVICE_ID |
5/17/2018 13:09:48+00:00 | ID1 |
5/20/2018 15:09:48+00:00 | ID2 |
5/17/2018 11:09:48+00:00 | ID3 |
5/14/2018 19:09:48+00:00 | ID1 |
5/22/2018 18:09:48+00:00 | ID2 |
5/17/2018 14:09:48+00:00 | ID1 |
TABLE2
CHILD_DEVICE_ID | PARENT_DEVICE_ID |
ID1_1 | ID1 |
ID1_2 | ID1 |
ID1_3 | ID1 |
ID1_4 | ID1 |
ID2_1 | ID2 |
ID2_2 | ID2 |
ID3_1 | ID3 |
ID3_2 | ID3 |
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_DATE | PARENT_DEVICE_ID | prev_7_days_used_flag |
5/17/2018 13:09:48+00:00 | ID1 | 1 |
5/20/2018 15:09:48+00:00 | ID2 | 1 |
5/17/2018 11:09:48+00:00 | ID3 | 0 |
5/14/2018 19:09:48+00:00 | ID1 | 1 |
5/22/2018 18:09:48+00:00 | ID2 | 1 |
5/17/2018 14:09:48+00:00 | ID1 | 1 |
Is it possible to do something like this in alteryx? Thank you
Solved! Go to Solution.
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
Hi kbalber,
sorry, you are right i didn't include everything in the data set. Here is the edited version
TABLE1
USED_DATE | PARENT_DEVICE_ID |
| |
5/17/2018 13:09:48+00:00 | ID1 | ID1_4 | |
5/20/2018 15:09:48+00:00 | ID2 | ID2_1 | |
5/17/2018 11:09:48+00:00 | ID3 | ID3_2 | |
5/14/2018 19:09:48+00:00 | ID1 | ID1_2 | |
5/22/2018 18:09:48+00:00 | ID2 | ID2_2 | |
5/17/2018 14:09:48+00:00 | ID1 | ID1_3 |
TABLE2
CHILD_DEVICE_ID | PARENT_DEVICE_ID |
ID1_1 | ID1 |
ID1_2 | ID1 |
ID1_3 | ID1 |
ID1_4 | ID1 |
ID2_1 | ID2 |
ID2_2 | ID2 |
ID3_1 | ID3 |
ID3_2 | ID3 |
Table 1 Output
USED_DATE | PARENT_DEVICE_ID |
| prev_7_days_used_flag | |
5/17/2018 13:09:48+00:00 | ID1 | ID1_4 | 1 | |
5/20/2018 15:09:48+00:00 | ID2 | ID2_1 | 0 | |
5/17/2018 11:09:48+00:00 | ID3 | ID3_2 | 0 | |
5/14/2018 19:09:48+00:00 | ID1 | ID1_2 | 0 | |
5/22/2018 18:09:48+00:00 | ID2 | ID2_2 | 1 | |
5/17/2018 14:09:48+00:00 | ID1 | ID1_3 | 1 |
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 :)
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. @ RogerS You solution palatially solves it
I attached the workflow
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:
Let me know if you need any more help with this.
Thanks,
Ken