Alteryx Designer Discussions

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

check if the ID exists...

7 - Meteor

Hi ,

Thanks for your time. I have this scenario where i need to look if an ID exists in the prior month or prior consecutive months or in any month and the months are not consecutive....

 

I have the dataset something like below.. Inventory Number  which will have rows when there is failure...each inventory number will have multiple records (12 months) if the inventory number is failed in each month then there will be 12 records if it is not failed at all then there is no record at all for that inv number.. what i need to derive is something like MonthlyFailStaus and MonthlyFailStatusDesc so that the records can be grouped into certain buckets

 

Failed only once - This particular inventory number is failed (think of it as a job failed for this server) only once in the entire 12 month (there is only 1 record)

Failed few times in the same month - This inventory number failed few times in the same month say for ex: Inv123 job failed on 5th, 10th, 25th

Failed in consecutive 3 months - This inventory number failed in the consecutive months (calendar is Jan to Dec) - say for example it failed in Jan, Feb , March 

Repeating Fail - This inventory number failed few times but the months are NOT consecutive say for ex: inv123 failed in Jan then failed in Apr then failed in Jul - these are not consecutive

Failed in consecutive 2 months - this inventory number failed in the consecutive months like Jan, feb  or any month like Oct,Nov  (failed 2 times in 2 months straight)

Failed in more than 3 Consecutive Months:this inventory number failed more than 3 times in the consecutive months like Jan, feb, march and april

 

any suggestions on the best approach.. i have few diff data sources in alteryx workflow where i use join and union --thanks a lot 

Alteryx Certified Partner

Hi @Raj_007 ,

 

I've created a workflow that I think answers your question.

AngelosPachis_0-1609875731213.png

 

Since we are mostly interested at what months and year each failure happened, I've used a formula tool with a datetrim function to force each start date to the first of the month.

AngelosPachis_1-1609875988642.png

 

That allowed me to then use a summarize tool to group on the inventory number, group on the new field and count the number of failures occurring for each inventory each month

 

AngelosPachis_2-1609876063418.png

 

Then, with a multi-row formula tool, I was allowed to decide whether for a particular inventory, those failures occurred in consecutive months and if yes, then flag them with a "C". If not, flag them with a "N".

 

AngelosPachis_3-1609876145504.png

 

Note how the first failure for each Inventory No does not have a flag assigned. That will be important later on. Then again by using a summarise tool, I was able to group on the Inventory No and concatenate the field containing the flag.

 

AngelosPachis_4-1609876284174.png

Hence, now I know the following:

 

  1. Inventory 1000101 failed once in a month,
  2. Inventory 1000102 failed three times in a month
  3. Inventory 1000103 failed once in 3 consecutive months (CC= 2 times +1 the first time it failed)
  4. Inventory 1000104 failed once in 4 non-consecutive months (NNN= 3 times +1 the first time it failed)

The final step was to implement this logic inside a formula tool. It's not very delicate, but it get's the job done.

 

Screenshot 2021-01-05 195606.jpg

 

Let me know if that was what you were after and please feel free to ask any questions. Thanks for the challenge.

 

Regards,

 

Angelos

7 - Meteor

Hi Angelos,

Thank you so much for your time, let me first try to understand so please give me sometime to look at it and see how it is working... i will get back to you for any questions then we can flag this thread as closed

7 - Meteor

Hi Angelos,

The sample dataset is just for testing. Say for ex: we would like have the dataset as is in the final output file (say we write the final output to an excel file)

and we would like to keep the dataset with duplicates the reason is we also have some other columns where the inventory number will have duplicates because of some diff values in a field for ex;

Inv12345 which has 3 consecutive fails and some other columns like status has values like cancelled, in progress etc. 

 

what that means is I still want to keep all the records with duplicates but flag or compute what you provided as solution - what are my options

 

i do not want just the unique inventory numbers i want everything with duplicates also

 

like below - thanks for your time

InventoryNoStartDateMonthlyFailStatusMonthlyFailStatusDesc
10001011/10/2020SingleFailed only once
10001021/10/2020MultipleFailed few times in the same month
10001021/15/2020MultipleFailed few times in the same month
10001021/21/2020MultipleFailed few times in the same month
10001031/15/2020MultipleFailed in consecutive 3 months
10001032/10/2020MultipleFailed in consecutive 3 months
10001033/10/2020MultipleFailed in consecutive 3 months
10001041/25/2020MultipleRepeating Fail
10001045/10/2020MultipleRepeating Fail
10001047/10/2020MultipleRepeating Fail
100010410/23/2020MultipleRepeating Fail
10001058/15/2020MultipleFailed in consecutive 2 months
10001059/12/2020MultipleFailed in consecutive 2 months
100010610/15/2020SingleFailed only once
10001074/15/2020MultipleFailed few times in the same month
10001074/21/2020MultipleFailed few times in the same month
10001074/29/2020MultipleFailed few times in the same month
10001084/15/2020MultipleRepeating Fail
10001086/15/2020MultipleRepeating Fail
10001088/15/2020MultipleRepeating Fail
10001096/15/2020MultipleFailed in consecutive 3 months
10001097/21/2020MultipleFailed in consecutive 3 months
10001098/30/2020MultipleFailed in consecutive 3 months
10001107/15/2020MultipleFailed in more than 3 Consecutive Months
10001108/13/2020MultipleFailed in more than 3 Consecutive Months
10001109/12/2020MultipleFailed in more than 3 Consecutive Months
100011010/10/2020MultipleFailed in more than 3 Consecutive Months
100011011/10/2020MultipleFailed in more than 3 Consecutive Months
100011012/12/2020MultipleFailed in more than 3 Consecutive Months
10001101/13/2021MultipleFailed in more than 3 Consecutive Months
Alteryx Certified Partner

Hi @Raj_007 ,

 

Do you mean something like this as an output?

 

 

AngelosPachis_0-1610042327629.png

 

Alteryx Certified Partner

Hi @Raj_007 ,

 

Was that helpful at the end? Did you manage to solve this?

 

Thanks,

 

Angelos

7 - Meteor

Hi Angelos, thank you so much. It really worked. find and replace tool - never used it. i thought we might have to do some joins 

but it was really simple with find and replace tool - thanks a lot

Labels