This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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
I've created a workflow that I think answers your question.
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.
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
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".
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.
Hence, now I know the following:
Inventory 1000101 failed once in a month,
Inventory 1000102 failed three times in a month
Inventory 1000103 failed once in 3 consecutive months (CC= 2 times +1 the first time it failed)
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.
Let me know if that was what you were after and please feel free to ask any questions. Thanks for the challenge.
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