Forum Discussion
AdministratorAdding a "1" or a "0"
I separate the transformation steps. Not sure if this would improve the performance. You could have a try.
1. First click on Group By under Transform tab. Switch to Advanced section. Group on Employee column and perform the following two operations to add two new group columns:
Max WTE: Max on WTE column;
All Data: All Rows
2. Expand the All Data column.
3. Add a conditional column: Add column tab > Conditional column.
4. Remove unnecessary columns and resort columns.
Result:
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.I think of another method which can meet your need. Instead of comparing every WTE with the maximum WTE, I sort the grouped tables descendingly by WTE, then add an Index column starting from 0 in every grouped table. Then mark rows where index value is 0 as 1 in Head Count column. Open Query 2 in the new attached sample file to see detailed steps.
Result
Best Regards,
JingSorry for the late reply. The operation needs to be modified a little bit. When grouping by, switch to Advanced option and group by Employee and Dept columns at the same time. See below image.
Then add an Index column to every grouped table, starting from 1 with increment 1. Mark every row with index 1 as 1 and mark other rows as 0. See Query 1 in the attachment.
Best regards,
Jing
Hi Jing, thank you so much for all your assistance, This has helped me immensely and I really appreciate it as the time saved is enormous. Your patience and explanations were fantastic. Take care - Ciaran
You just need to modify the Group step in my last sample file. Group by only Employee column rather than Employee&Dept. Keep the other steps same. See the attached file.
Jing
Community Support
Sorry for the late reply. The operation needs to be modified a little bit. When grouping by, switch to Advanced option and group by Employee and Dept columns at the same time. See below image.
Then add an Index column to every grouped table, starting from 1 with increment 1. Mark every row with index 1 as 1 and mark other rows as 0. See Query 1 in the attachment.
Best regards,
Jing
AdministratorHi Jing, thank you so much for all your assistance, This has helped me immensely and I really appreciate it as the time saved is enormous. Your patience and explanations were fantastic. Take care - Ciaran
- v-jingzhang
Hi Ciaran, I'm very glad my solutions are helpful. You are welcome! - Jing
- Syndicate_Admin
Hi Jing, sorry to bother you again but I have discovered that when trying to calculate the Organisational Headcount and the person has 2 jobs with the same WTE but different contract types i.e. one permanent the other is temporary that the solution adds the Headcount of "1" by default to the temporary post when I need it to default to the permanent post (for those that meet that criteria). I have added another table to demonstrate what I mean. I appreciate that contract type wasn't in my original data but I only realised the significance when double checking the results. I have tried adding the contract type to the grouping and indexing that but again the figures are incorrect. Any assistance would be greatly appreciated and apologies for my error with the original data. For ease I have highlighted in yellow where the previous solution is going wrong.
- v-jingzhang
Please download the attachment to see the new code.
The difference is in the highlighted row. When one person has only one post in a Dept and that post is a temporary post, I give it 1. But in your actual result, you give it 0. Can you ensure that when the only post is a temporary one, it is expected to be 1 or 0? If it should be 0, I will modify the code.
Jing