The data is about students for one year.
There are seven reasons for moving out of the college (ex - Faculty, Facilities etc,)
Now I wanna create a workflow which gives the count of students who left with each reason.
Suppose in month of January if 20 students left with 5 reasons among seven I want the list and those seven reasons should come in the output with count as Zero for reasons which are not there.
I have extracted the data and filtered the columns such as student name, student Id and date of moving out and reason
I have grouped the data with grouping column as reason and aggregation as count.
I got the count of students with each reason for that month.
I got struck at how to get the reasons which are not available in that month data.
How to add them to each month data and their count as Zero.
it is difficult to follow you explanation without any test data or a workflow. Can you share one or both please? In case the data contains sensitive information, like names, please anonymize it in advance i.e. by removing all but one character or share the data privately.
PS: Reading through your description a few times I believe from what I understood with “I got struck at how to get the reasons which are not available in that month data. How to add them to each month data and their count as Zero.” that the Value Lookup Node or by utilizing a more complex join approach, you might get the desired results.
Very interesting analysis. Like @mwiegand mentioned, if you share the workflow with some dummy data, it will give some idea on how to achieve the result. However, here are some tips.
Create a database table or excel file with the list of existing reasons; maintain date or month and year, first time the reason appeared
Upfront calculate the reasons that are existing and match it with new set of data
Any non-existant reason can be added to the table with date or month and year
From the date or month / year column you can get the new reasons that appeared in that month.
It is will be useful to do the analysis at a later data as well when a reason appeared.
Now I wanna groupby monthly followed by group by reasons which are existed for that particular month and Reason which is not available also should be there with count as Zero.
Please help me with the logic.
Thanks in Advance,
Subramanyam Kinthada Dummydata.xlsx (10.3 KB)
Thanks a lot for the above suggested method.
Both are working fine, but I am not getting the other columns such as student name, ID, and all.
How to get those all columns?
I am not sure what you mean by getting the other columns such as student name, ID. Here the data is kind of summarised. For example., if a reason appears in a month for more than 1 student then how do you want to represent two or three students with summarised data. In the dummy data shared by you, for the month 2023 Feb, reason E was used by 2 students. Now, you can concatenate or list student IDs in one column but, in my view, you cannot get one row for one student unless I am missing something. If one reason is used by one and only one student, then it is possible which isn’t the case.
I am not getting it either. May I suggest, as the language barrier seems the primary challenge, that you @Subramanyam write down what you aim for in your mother tongue and using Google Translate, ChatGPT or any other translation service?