DATA VALIDATION ST4 TEST RESULTS SUMMARY FOR 4/11/27

APPLICATION

TOTAL   TABLES EXECUTED

TABLE/S   EXCLUDED

NO   OF  TABLE/S WITH   FAILED TEST CASES

NAME   OF TABLE/S WITH   FAILED TEST CASE

EDW

47

Table Excluded 1   Table Excluded 2   Table Excluded 3   Table Excluded 4   Table Excluded 5

3

OMNI_LH_EXTRACT   OMNI_LN_EXTRACT   OMNI_LP_EXTRACT

ENVIRONMENT

ST4

ANALYSIS

STATUS

OMINI_LH_EXTRACT /   AHLH_TOTAL_INT_PAID-TEST CASE 44 / RCA 1   OMINI_LH_EXTRACT / AHLH_LOAN_INACTIVE_DATE-TEST CASE 45 / RCA 2                                            ..    (Until All Tables Test cases are listed   with RCA)     NOTES - FAILURES WILL BE LISTED IN CRITICALITY ORDER

RED

AMBER

GREEN

                                                                                                                                                                                                                                                                                                                                                                                     In the above diagram is the Summary sheet some of the fields need to show dynamically based on other sheets data.

In my file Except Summary Sheet, Analysis, Tables Excluded remaining sheets are the Tables Names which are executed, so we should not consider these  3sheets  (Summary Sheets, Analysis, Tables Excluded) while applying formula for getting the count.

Total Tables Executed The No of sheets in that file - 3

Table/s Excluded We have a separate sheet for this (TABLES EXCLUDED). We will mention all the tables which are not executed in this sheet. From this sheet we need to pull out the data in the Summary Sheets as show in the above diagram into a single cell (i.e Multiple rows will be shown in a single row in the Summary sheet one below the other).

TABLES EXCLUDED

TABLES EXCLUDED 1

TABLES EXCLUDED 2

TABLES EXCLUDED 3

TABLES EXCLUDED 4

TABLES EXCLUDED 5

NO OF TABLE/S WITH FAILED TEST CASES- Except in Main Sheet, Analysis, Tables Excluded in remaining sheets we have a column TEST_RESULT if that column has FAIL we should consider the Count as 1 for each sheet. Even if the sheet has more than1 FAIL we should consider the count as 1 (Because the each table may have many FAIL condition but we should consider as 1)

NAME OF TABLE/S WITH FAILED TEST CASE In this we have concatenate the sheet Name and Failed Test Case in (Column B). For all the sheets we have to apply the formula and each Failed test case we have show one below the other in a single cell.

ANALYSIS- This we will have a separate tab call (Analysis) tab where it will have three columns. We have to concatenate all these three column and show it in Summary Sheets. This is also same way we have to show one below the other for Each Analysis Rows in a single Cell in the Summary sheet.

TABLE NAME	TABLE TEST CASE FAILED	RCA
OMINI_LH_EXTRACT	AHLH_TOTAL_INT_PAID-TEST   CASE 44	RCA 1
OMINI_LH_EXTRACT	AHLH_LOAN_INACTIVE_DATE-TEST   CASE 45	RCA 2
OMNI_LN_EXTRACT	RDBFEED_PMT_AMT-TEST   CASE 16	RCA 3
OMNI_LP_EXTRACT	AHLP_PMT_TYPE-TEST   CASE 15	RCA 4

Reference Document

I can provide the reference document of Excel file if someone can give the personal Email ID please as I cannot able to attach the file.

• Edited by Kiran Kandavalli Tuesday, December 23, 2014 10:21 PM

Hi,

Based on your description, if you want to show data dynamically based on other sheets, we could use Vlookup formula, please see the below sample.

Formula: =H2 & " / " & VLOOKUP(H2,H1:J5,2,0) & " / " & VLOOKUP(I2,I1:J5,2,0)

If you have further question, please upload sample file via OneDrive and share the link here.

(Please note: Make sure the sample has no privacy content, and it could be shared in open forum)

If the sample can't be public and has sensitive content, please send to me via Email (ibsofc@microsoft.com). But this address has some issue now, we need a short time to fix it. I'll let you know in time when it has been fixed.

Thanks for your understanding.

Regards,

George Zhao
TechNet Community Support

Hi George,

I had sent an email to your email ID (ibsofc@microsoft.com), but it is not delivered to you.

Thanks!

Kiran

Hi Kiran,

Would the above formula reach your needs?

As I mentioned above, the collection Email address has some issue now, we are working on it and fix it in a short time. I'll let you know at the first time when it works again.

Regards,

George Zhao
TechNet Community Support