Audimation Services has been acquired by CaseWare International Learn More.

X
Icon


Blog Image

Tech Tip: Understanding Join and Visual Connector


Using IDEA’s Join and Visual Connector features can help you search for matches and correlations between different data sets, but they are often confused with one another.

Join allows you to perform matches on up to eight fields. Visual Connector performs matches on just one field. Each field used to match must be of the same datatype in each database. Never match on virtual fields!

In the event that you have more key fields to match than are allowed, you can create a concatenated key. The basic formula for a concatenated key is: TEXT_1 + TEXT_2 + Text_3, the plus sign used to string character fields together. Any numeric values can be converted to character using the @Str function and any date fields converted using the @DtoC function. Let’s convert the values below to a concatenated key:
 
@DtoC(INVOICE_DATE,”YYYYMMDD”) +”_” + INVOICE_NO + “_” + @Str(INVOICE_AMT,0,2)
 

INVOICE_DATE INVOICE_NO INVOICE_AMT
10/24/2018 135288 1,196.00

 
The result will be: 20181024_135288_1196.00

IDEA Join is intended for ONE to ONE or MANY to ONE relationships, the Secondary file limited to 1 record per Key Field(s) used for match. The interactive chart demonstrates what will happen in the case of a many to many relationship. IDEA will always link to the 1st record in the Secondary file and ignore the 2nd record – use Visual Connector for these situations.

Explore the Interactive Diagram Now!

If you need assistance using Join, Visual Connector or any other feature in IDEA, contact us at [email protected] or call 888.641.2800 ext. 4 and we will walk you through each step.


Best Practices , CaseWare IDEA , Tech Tip



Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.


Related Posts
Public Sector
Jan 01 Government systems often relate to the payment of benefits, grants, assistance, etc. to individuals and to organizations. The tests with IDEA tend to be specifi...
From T & E Reviews to Continuous Auditing
May 20 How Data Analysis Tools Empower the Internal Audit Function   Our ominous economic situation has prompted many companies to rethink how they conduct busi...
Tech Tip: IDEA 11 Labs: WORD FIND and TEXT ANALYSIS
Oct 27 Utilize these plugins to enhance and/or improve the automation of your workflow.   Word Find   This plugin creates a summary of all the words from...
BROWSER NOT SUPPORTED

This website has been designed for modern browsers. Please update. Update my browser now

×