How to perform DATA PROFILING with user defined queries
search cancel

How to perform DATA PROFILING with user defined queries

book

Article ID: 204159

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Hi,

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

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?

 

Environment

TDM Portal 4.9.0

CA Test Data Manager

Resolution

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.