Data Validation

[vijaylnyadav]

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.

[messi007]

@vijaylnyadav,

Could you complete manually the column Error or Warning.

It will help to understand your needs.

Regards,

[vijaylnyadav]

@messi007 

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

[leon2020 · Accepted solution]

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.

[vijaylnyadav]

@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

[leon2020 · Accepted solution]

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.