We are trying to execute Data profiling in the CA TDM portal. We are trying to profile data using the below query :
, MAX(con.WSIB_WCB_ID) AS ClaimantWCBID
,MAX(con.FirstName) as ClaimantFirstName
,MAX(con.LastName) as ClaimantLastName
,MAX(con.Name) As ClaimantName
,MAX(con.WSIB_Website) as ClaimantWebsite
,MAX(con.SBN_WC) AS ClaimantContactSBN_WC
,COALESCE(CONVERT(VARCHAR(8), MAX(con.DateOfBirth), 112), - 1) as ClaimantDateOfBirth
,COALESCE(CONVERT(VARCHAR(8), MAX(con.DateOfDeath), 112), - 1) as ClaimantDateOfDeath
,MAX(con.LicenseNumber ) as LicenseNumber
FROM dbo.cc_claim c
INNER JOIN dbo.cc_claimcontact cc ON cc.ClaimID = c.ID
INNER JOIN cc_contact con ON cc.ContactID = con.ID
GROUP BY c.ClaimNumber
Order by c.ClaimNumber
Even though we can define the joins while defining the model and where clause while doing a find and reserve, I couldn’t find a way where I can
1) Define the Max functions on the required columns and Group by clause while defining the model or while doing a find and reserve on data.
Could you please help with this issue?
TDM Portal 4.9.0
CA Test Data Manager
The only possible way to accomplish this would be to encapsulate your query in a view directly on the database.
You would also need to be running TDM Portal 4.9.1+ in order to have support for using views.