Consider the following sample data,
Roll no | Seat number | Subject | Marks | Error or Warning |
1 | 1111 | English | 89 | |
2 | 2222 | Hindi | 78 | |
3 | 3333 | Sanskrit | 98 | |
2 | 2222 | Hindi | 77 | |
1 | 1111 | English | 84 | |
4 | 4444 | Marathi | 76 | |
3 | 3333 | Sanskrit | 98 | |
6 | 6666 | Gujarati | 68 | |
2 | 2222 | Hindi | 71 | |
4 | 4444 | Marathi | 76 | |
2 | 2222 | Hindi | 78 | |
6 | 6666 | Gujarati | 66 |
Condition: -
if roll no and seat no are same, validate subject and verify marks are different - Warning Message If roll no & seat number are same, validate subject and verify marks are same - Error Message |
Please suggest me a workflow or solution so that i can complete my assignment.
Solved! Go to Solution.
Could you complete manually the column Error or Warning.
It will help to understand your needs.
Regards,
Roll no | Seat number | Subject | Marks | Error or Warning |
1 | 1111 | English | 89 | Warning |
2 | 2222 | Hindi | 78 | Warning |
3 | 3333 | Sanskrit | 98 | Error |
2 | 2222 | Hindi | 77 | Warning |
1 | 1111 | English | 84 | Warning |
4 | 4444 | Marathi | 76 | Error |
3 | 3333 | Sanskrit | 98 | Error |
6 | 6666 | Gujarati | 68 | Warning |
2 | 2222 | Hindi | 71 | Warning |
4 | 4444 | Marathi | 76 | Error |
2 | 2222 | Hindi | 78 | Warning |
6 | 6666 | Gujarati | 66 | Warning |
if marks are same then error and if marks are different then warning message for same roll number
Interesting one to solve, sure there are many ways but here's my approach:
Summarized by roll no and seat no, then concatenated subject and min/max marks for checking. Note the concat has no comma separation - this will be a temporary field and used in a formula later to deduce if subject is validated.
Then joined the output of that summarise tool to the original input, joining by seat no and roll no, should result in 0 data from the L &R anchors and the same number of rows (12) in the Join anchor. Following this, used the formula to create the Error or Warning field and select the desired columns for the final output/browse. Attached workflow.
@leon2020 can u please suggest some other solution for the same problem?
There is not harm in this solution but as you mentioned it can be solved by many ways.
Therefore, please suggest some another solution,
Thanks
In the Summarize tool, you could Group By Roll no. and Seat no. and Count Distinct Subject and Count Distrinct Marks. In hindsight, that might be a better approach!
The formula tool can then be adjusted thus:
IF [CountDistinct_Subject]=1 AND [CountDistinct_Marks]=1 THEN "Error: Subject and Marks are the same"
ELSEIF [CountDistinct_Subject]>1 THEN "Error: Subjects differ"
ELSE "Warning: Marks differ"
ENDIF
Then join the two together by specific fields roll no and seat numbers, selecting all the left fields and the right error or warning (assuming the left anchor is the original dataset and the right anchor is the output from the formula following the summarize).
Depending on how many duplications you may expect, you might order by seat number and roll number, and use a multi-row formula to check if the row above contains the same values for roll no, seat number, subject and marks. This would also flag the first occurrence of each row/seat no, which you can either use or work around.