How to perform DATA PROFILING with user defined queries
search cancel

How to perform DATA PROFILING with user defined queries


Article ID: 204159


Updated On:


CA Test Data Manager (Data Finder / Grid Tools)



We are trying to execute Data profiling in the CA TDM portal. We are trying to profile data using the below query :


SELECT    c.claimnumber
  , 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

  WHERE cc.retired=0
  and con.retired=0
  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.