Create Dataset using Join

• By using this document user will leran to make dataset by using Join.

What is Join?

• A JOIN clause is used to combine rows from two tables, based on a related column between them.

Objectives

• Create Dataset using Joins.

Prerequisites

• For this example we are using Sales.ds, Insurance Claims.ds & Insurance.ds

Download join.zip file from link given join.zip

• After downloading the zip file, add it into the AIV. To add it into AIV follow this Link

Skill Level Required

• Basic knowledge of Joins & SQL.

• There are three types of Joins available:-

I. Inner Join
II. Left Outer Join
III. Right Outer Join

I. Inner Join

Inner Join:- Returns records that have matching values in both tables.

1. Log in to AIV using your respective credentials.

2. Go to Hamburger Menu > MasterData > Dataset.

3. To know more in details about Dataset Section Click here .

4. User can create dataset using 2 options:-

I. From the Footer Menu

II. From the Context Menu

5. Click on Create Dataset option & fill the below details:

• Name: Join Dataset
• Datasource: Join

6. Fill the details as shown in the image below:-

• Dataset (at the left side): Insurance Claims.ds
• Dataset Columns: Select all columns available
• Select Join: Inner Join
• Dataset (at the right side): Insurance.ds
• Dataset Columns: Select all columns available

7. User can select columns as many they want to see in the output.

8. Click on the key button as shown in the image above.

9. Select Country From both Dataset Keys.

10. Click on (+) icon below Key Dataset box.

11. Select Country||Country from Key Dataset Drop-Down.

12. Click on Output Columns Tab next to General Tab.

13. Here user will notice the columns name Insurance Claims

    & Insurance
    . Same Country column for Insurance Claims & Insurance dataset.

14. Click on Preview button.

15. Click on submit button & your dataset will be created.

II. Left Outer Join

Left Outer Join:- Returns all records from the left table, and the matched records from the right table.

16. Follow the same steps to 1 to 5 & countiue to follow below steps in order to make dataset with Left Outer Join.

17. Fill the below details in Create Daataset window:-

• Name: Left Join Dataset
• Datasource: Join

18. Fill the below details in Create Dataset window:-

• Dataset (at the left side): Sales.ds
• Dataset Columns: Select country, countrycode, orderDate, productCode, quantityOrdered & priceEach
• Select Join: Left Outer Join
• Dataset (at the right side): Insurance.ds
• Dataset Columns: Select SrNo, Policy_Date, CustomerIDMonth, Year & Country

19. Click on the key button as shown in the image above.

20. Select Country From both Dataset Keys.

21. Click on (+) icon below Key Dataset box.

22. Select Country||Country from Key Dataset Drop-Down.

23. Click on Output Columns Tab next to General Tab.

24. Here user will notice the columns name Sales

    & Insurance
    . Same Country column for Sales & Insurance dataset.

25. Click on Preview button.

26. Scroll the Vertical & Horizontal bar to see the dataset preview.

27. Compare Sales

    & Insurance
    to see the difference in dataset.

28. This dataset includes the all the data of Sales.ds table & matched data of Insurance.ds table.

29. Click on submit button & your dataset will be created.

III. Right Outer Join

Right Outer Join:- Returns all records from the right table, and the matched records from the left table.

30. Follow the same steps to 1 to 5 & countiue to follow below steps in order to make dataset with Right Outer Join.

31. Fill the below details in Create Dataset window:-

• Name: Right Outer Join
• Datasource: Join

32. Fill the below details in Create Dataset window:-

• Dataset (at the left side): Insurance.ds
• Dataset Columns: Select SrNo, Policy_Date, CustomerIDMonth, Year & Country
• Select Join: Right Outer Join
• Dataset (at the right side): Sales.ds
• Dataset Columns: Select country, countrycode, orderDate, productCode, quantityOrdered & priceEach

33. Click on the key button as shown in the image above.

34. Select Country From both Dataset Keys.

35. Click on (+) icon below Key Dataset box.

36. Select Country||Country from Key Dataset Drop-Down.

37. Click on Output Columns Tab next to General Tab.

38. Here user will notice the columns name Insurance

    & Sales
    . Same Country column for Insurance & Sales dataset.

39. Click on Preview button.

40. Scroll the Vertical & Horizontal bar to see the dataset preview.

41. This dataset include the all data of Sales.ds table & matched data from the Insurance.ds table.