Galvanize Academy Analyzing data in Ana... (ACL 104 V1 CPE) Review Quiz - Analyzing PDF

Preview

Summary

Download Galvanize Academy Analyzing data in Ana... (ACL 104 V1 CPE) Review Quiz - Analyzing PDF

Description

Analyzing data in Ana... (ACL 104 V1 CPE) (https://academy.wegalvanize.com/learner/courseinfo

 You completed this test on 17/07/2020, 12:52 Your score is 100.00%

CORRECT

You're analyzing vendor invoices and you want to know how many invoices fall within certain ranges (e.g. if all the items were split into 5 equal parts, which items fall into the first range, what's their total value, etc.) Which Analycs command would achieve this?

STRATA RANGE



STRATIFY AGE

A – Oops! There is no STRATA command. Instead, use the STRATIFY command to group data into numeric ranges. B – Oops! There is no RANGE command. Instead, use the STRATIFY command to group data into numeric ranges. C – That's right! The STRATIFY command is used to group data into numeric ranges known as ‘strata.’ D – Oops! The AGE command is used to group data into ranges based on age, for example, 0-30 days, 31-60 days, etc. Instead, use the STRATIFY command to group data into numeric ranges.

CORRECT

If you need to group multiple occurrences of the same value in more than one field, which Analytics command could you use?

CLASSIFY



SUMMARIZE STRATIFY GROUP

A - Oops! The CLASSIFY command is used to group values within ONE field. If you want to group values within MULTIPLE fields, use the SUMMARIZE command. B - That's right! The SUMMARIZE command is used to group values within MULTIPLE fields. For example, if you wanted to find out if the same credit card was used for multiple transactions on the same date, you would use the SUMMARIZE command. C - Oops! The STRATIFY command is used to group data into the ranges they fall within. If you want to group values within MULTIPLE fields, use the SUMMARIZE command. D - Oops!The GROUP command is an advanced scripting command (taught in Scripting in Analytics – ACL 303) which is used to run multiple commands at the same time. If you want to group values within multiple fields, use the SUMMARIZE command.

CORRECT

You have transaction tables for January, February, and March, but you want to analyze these tables as one Q1 table. What is the best way to achieve this?

JOIN the tables RELATE the tables



APPEND the tables

A - Oops! The JOIN command is best used when you want to combine data horizontally on key fields (and additional fields become available once linked). To analyze all three tables as one Q1 table, instead use EXTRACT/APPEND. B - Oops! The RELATE command is best used when you want to combine data horizontally on key fields (and additional fields become available once linked). To analyze all three tables as one Q1 table, instead use EXTRACT/APPEND. C – That's right! APPEND is the best approach for combining tables containing the same fields into one table since they are stacked (appended) together vertically.

CORRECT

When you EXTRACT a table, a new table is created.



True False

The EXTRACT command is used to extract data to a new table. The Append option is used to append data to an existing table during an extraction.

CORRECT

When you JOIN two tables, a new table is created.



True

False

When you JOIN two tables, an entirely new table is created. When you relate two tables, they are virtually linked on key fields.

CORRECT

When you relate two tables, a new table is created.

True



False

When you RELATE two tables, they are virtually linked on key fields. When you join two tables, an entirely new table is created.

CORRECT

You want to identify if there were any purchases made from vendors who do NOT exist in the vendor master data. Using Purchases as the primary table, which JOIN type is the best option to achieve this?

Matched



Unmatched Primary

Matched – Oops! This produces only matches. You want to identify purchases made from vendors that do NOT have a corresponding match in the vendor master table. An Unmatched Primary Join type would be the best option for achieving this. Unmatched Primary – That's right! This identifies all purchases made by vendors who do not exist (do not have a match) in the vendor master table.

CORRECT

You want to JOIN two tables to identify if there were any purchases made from vendors who don't exist in the vendor master data. Which tables are the best choices for primary and secondary tables?

vendor_master

secondary

transactions

primary

Since there could be multiple transactions for the same vendor, transactions should be your primary table. Otherwise, every transaction except for the first for that vendor will be dropped and excluded from the joined table. For this reason, vendor_master should be your secondary table.

CORRECT

You want to join your vendor master data to an invoices table using a basic matched JOIN. Which table should you use as your primary and secondary?

vendor_master

secondary

invoices

primary

Since there could be multiple invoices for the same vendor, the invoices table should be your primary table. Otherwise, every invoice except for the first invoice for that vendor will be dropped and excluded from the joined table. For this reason, vendor_master should be your secondary table.

CORRECT

Which of the following is true?

When using RELATIONS, all fields must be identical between the two tables (same order, same field lengths, same data types) When using JOIN, all fields must be identical between the two tables (same order, same field lengths, same data types) When using APPEND, all fields must be identical between the two tables (same order, same field lengths, same data types)



None of the above

A – Oops! Only the key fields that are used to join or relate the two tables must have the same structure (same field type, format, and length). It is when using EXTRACT/APPEND that you must make sure all fields are identical. B – Oops! Only the key fields that are used to join or relate the two tables must have the same structure (same field type, format, and length). It is when using EXTRACT/APPEND that you must make sure all fields are identical. C – Oops! When using APPEND, fields should be of the same data type category, but don't need to be exactly the same format and length. APPEND carries out automatic harmonization to resolve differences between tables. It is when using EXTRACT/APPEND that you must make sure all fields are identical. D – That’s right! RELATIONS, JOIN, and APPEND don't require fields to be exactly the same. When using EXTRACT/APPEND, all fields must be identical, otherwise you run the risk of skewed or corrupted data.

CORRECT

Which of the following sorting options produces a new table?

Quick Sort



SORT

INDEX

A – Oops! Unlike the SORT command, QuickSort does not produce a new table; it simply re-arranges the way the data is displayed in the view but does not adjust the underlying data. B – That's right! When the SORT command is run, you must specify a name for the new table that’s created. The newly sorted records are saved to a new table. C – Oops! Unlike the SORT command, the INDEX command doesn’t produce a new sorted table. Instead, an index file (.INX) is created and applied to the existing table.

CORRECT

You're analyzing vendor invoices and want to know which invoices are more than 31 days over due, which are more than 61 days overdue, which are more than 91 days overdue, etc. Which Analycs command would achieve this?

STRATA RANGE STRATIFY



AGE AGING

A – Oops! There is no STRATA command. Instead, use the AGE command to group data into ranges based on age. B – Oops! There is no RANGE command. Instead, use the AGE command to group data into ranges based on age. C – Oops! The STRATIFY command is used to group data into numeric ranges. Instead, use the AGE command to group data into ranges based on age. D – That's right! The AGE command is used to group data into ranges based on age, for example, 0-30 days, 31-60 days, etc. E – Oops! There is no AGING command. Instead, use the AGE command to group data into ranges based on age.

CORRECT

In the Inventory table, what is the Quantity on Hand (qtyoh) for Product Class (prodcls) 08?

19 8,000



31,398 169,285

Total the Quantity on Hand (qtyoh) field. In the TOTAL dialog box, use the IF button to add a filter for a Product Class of 08 (prodcls= "08"). 19 - Incorrect. You may have counted the number of records where the Product Class (prodcls) is 08. Use the Total command with an IF expression instead. 8,000 - Incorrect. You may have used the wrong field when totaling. Ensure you are working with the Quantity on Hand field (qtyoh). 31,398 - That's right. You totaled the Quantity on Hand (qtyoh) field with the IF expression included in the dialog box, prodcls = "08". 69,285 - Incorrect. This is the total of the Quantity on Hand (qtyoh) field. We only want to find the Quantity on Hand for Product Class 08. In the Total dialog box include the IF expression, prodcls = "08".

CORRECT

From the Inventory table, which location currently holds the most Quantity on Hand for one product class? Summarize by both Location (location) and Product Class (prodcls).

02 04



05 09

Summarize by Location (location) and Product Class (prodcls), subtotaling the Quantity on Hand (qtyoh). Then, sort the Quantity on Hand (qtyoh) in descending order. 02 - Oops! This is the Location that has the highest number of records in the table. We want to see which location has the highest Quantity on Hand for an individual product class. You will need to summarize on both Location (location) and Product Class (pordcls) together, subtotaling the Quantity on Hand (qtyoh). 04 - Oops! This is the Location that has the most number of records when Summarized by Location (location) and Product Class (prodcls). However, you need to also subtotal the Quantity on Hand (qtyoh). 05 - That's right. This is the result when you Summarize using both Location (location) and Product Class (prodcls), subtotaling Quantity on Hand (qtyoh). 09 - Oops! This is the Location that has the highest number of records in the table. You need to Summarize by both Location (location) and Product Class (prodcls), subtotaling on Quantity on Hand (qtyoh).

CORRECT

From the AR table, what is the total Transaction Amount of the accounts that are overdue by 90 days and over, as of 1st Jan 2017? Use the Age command.

295 8,219.66 199,659.93



225,116.12

Use the Age command, aging on DueDate and subtotaling the field amount. Adjust the aging periods in the dialog box so the highest number in the list is 89, which covers the accounts that are overdue by 90 days and over. 295 - Oops! This is the number of customers / records that are overdue 90 days and over. You need to find the total Transaction Amount (amount) for these customers. 8,219.66 - Oops! This is the total amount that is overdue from 90 to 119 days (using the default settings). You are looking for the total amount that is 90 days and over, so the last Aging Period in your list should be 89 days. 199,659.93 - Oops! Check the Cutoff Date is set to Jan 1st 2017, and that the last Aging Period in your list is 89 (which would find the accounts that are overdue by 90 days and over). 225,116.12 - That's right. This is the result using the Age command, subtotaling on amount and adjusting the aging periods so that the last aging period is 89.

CORRECT

Which of the below allows several fields to be re-ordered without creating a new table, and allows you to reference or reapply the same ordering at a later time?

The Sort command A QuickSort



An Index file There is no command available that achieves this

The Sort command - Oops! The Sort command creates a new table and has a larger resulting file size. Indexing the file reduces the file size and does not create a new table; it also allows for the same order to be saved and called at a later time. A QuickSort - Oops! A QuickSort can only sort one field at a time, and cannot be recalled at a later time. Indexing the file is the correct route to take. An Index file - That's right. By creating an Index file, you can reorder the fields without creating a new file and then reapply the fields later, which would be beneficial if you want to use less disk space and have a smaller resulting file size than if you used the Sort command. There is no command available that achieves this - Oops! Indexing the file allows for the fields to be re-ordered without creating a new table. It also has a small file size when compared to using the Sort command.

CORRECT

In the AR table, extract the records which have an invoice date of the 11th of August 2016, and the transaction type 'IN' to a new table. What is the total transaction amount of these records?

0 $3,603.15



$3,621.49 $468,880.69

Use the Extract command with the filter in the IF text box InvoiceDate = `20160811` AND type = "IN". 0 - Oops! You may want to check you entered the date in the correct format `20160811` and applied the filter correctly. $3,603.15 - Oops! Even though you applied the date filter, you also need to apply the filter type = "IN", before totaling the transaction amount for your extracted records. $3,621.49 - That's right. You used the Extract command with the filter in the IF text box, InvoiceDate = `20160811` AND type = “IN”. You then totaled the transaction amount of these filtered records to reach $3621.49. $468,880.69 - Oops! You may have totaled all of the transaction amounts in the AR table without first filtering or extracting those records which have the invoice date of `20160811` and type = "IN".

CORRECT

In the AR table, what is the total transaction amount that Sales Rep 00030 is accountable for? Note: You will need to relate the AR table with the Customer table using the key field Customer Number (no). This allows you to add the Sales Rep Number field (sales_rep_no) to the AR table.

0 $122,000



$41,004.17 $468,880.69

Relate the AR table with the Customer table using the Customer Number (no) as the key field. Add the Sales Rep Number field (sales_rep_no) to the AR table, then Summarize on Sales Rep Number (sales_rep_no). 0 - Oops! You may have related your tables using the wrong key field. Make sure to relate the two tables using the Customer Number (no) as the key field. $122,000 - Oops! This is the total credit limit of the Customers that Sales Rep 0030 is responsible for. You need to relate the AR table with the Customer table first, before summarizing on the Sales Rep Number (sales_rep_no) "00030" and totaling the transaction amount. $41,004.17 - That's right. Once the AR table is related to the Customer table, the Sales Rep Number field (sales_rep_no) can be summarized for the AR transactions with the transaction amounts totaled. $468,880.69 - Oops! You may have totaled all of the transaction amounts in the AR table without first relating the tables then filtering and totaling.

CORRECT

In the Dept table, the Dept Code (dept) field is the same as the Product Class (prodcls) field in the Inventory Table. Use these two fields as the key fields to join the Inventory table with the Dept table. How many Hand Tools and Power Tools are on order (qtyoo) in your new table?

37 7400



7,950 17,291

Join the Inventory table with the Dept table. Use the Inventory table as the primary table (key field: Product Class) and the Dept table as the secondary table (key field: Dept Code). In the joined table include all the fields from the primary table, and in the secondary table include only the Dept Name field. In your new table, total the Quantity on Order (qtyoo) if deptdesc = "Hand Tools" OR deptdesc = "Power Tools". 37 - Oops! You may have found the total number of records rather than the quantity on order, which requires totaling the field qtyoo with a filter for the deptdesc field. 7,400 - Oops! You may have only applied the filter to find the qtyoo of Hand Tools. You also need to apply a filter on the qtyoo of Power Tools. 7,950 - That's right! You have correctly joined and totaled your new table, using the Inventory table as the primary table and the Dept table as the secondary table. You then totaled the Quantity on Order (qtyoo) if deptdesc = "Hand Tools" OR deptdesc = "Power Tools". 17,291 - Oops! You may have totaled the quantity on hand for hand tools and power tools by filtering the quantity on hand (qoh) field instead of the quantity on order (qoo) field.

CORRECT

In the Empmast table, what is the total of the Bonuses and Commissions for those with the "MANAGER" job description?

$4,800 $19,519



$24,319 $115,798

Use the Total command with the filter job = "MANAGER". Total the bonus and commission amounts from the results and add the two totals together. $4,800 - Oops! This is the total of the bonus amounts for those with the job description of Manager. You need to also add the total of the commission amounts to this. $19,519 - Oops! This is the total of the commission amounts for those with the job description of Manager. You need to also add the total of the bonus amounts to this. $24,319 - That's right. You used the Total command with the filter job = "MANAGER" and totalled the bonus and commission amounts from the results and added the two amounts together. $115,798 - Oops! This is the total of the commission and bonus amounts added together, but without the filter applied. You need to also apply the filter where job = "MANAGER".

CORRECT

In the GeneralLedger table, how many records have a CreateDate in the months of June or July in 2016?

667



674 686 1,721

Apply the filter CreateDate >= `20160601` AND CreateDate = `20160601` AND CreateDate...