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
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.
- Syndicate_Admin
Thank you so much, this has resolved the formula and performance issues. This is just fantastic and it will save me so much time.
- Syndicate_Admin
Hi Jing, I thought you had cracked it and all seemed to be correct then I discovered later that when I worked the figures out manually they differeed. Upon checking I have discovered that your method worked to a point. The only issue that arises is when a person has two WTEs which match. For example a person who has two part time jobs which are 0.50 and .50. Your method records a 1 against each value however we can only count an employee once. Have you any ideas on how to solve that? Sorry for the confusion, in my excitement I thought we had it sorted. I have attached a sample highlighted yellow of where it has gone wrong
- v-jingzhang
It doesn't matter. I understand the scenario. My question is that when a person has multiple WTEs that match his maximum WTE, do you have any priority rule pointing that one specific WTE row must be marked as 1? Or you don't mind that so any one is ok to be marked as 1?
Jing
- Syndicate_Admin
Hi Jing unfortunately it does matter as it is counting those people twice in the headcount when it should only be once. For Exampe if the max wte is .5 in both posts, then your method is placing a 1 against both rows when it should be a 1 and then a zero. In excel we use a formula that compares the rows against the rows above and it returns the correct values. I need to replicate the process somehow. Thank you for your assistance.