Free QlikView QSDA2024 Practice Exams - Page 4 of 5

Question #31

Exhibit.

A chart for monthly hospital admissions and discharges incorrectly displays the month and year values on the x-axis.

The date format for the source data field "Common Date" is M/D/YYYY. This format was used in a calculated field named "Month-Year" in the data manager when the data model was first built.

Which expression should the data architect use to fix this issue?

A . Date(MonthStart([Common Date]),’MMM-YYYY’)
B . Date([Comraon Date],’MMM-YYYY’)
C . Date(MonthsStart([Common Date]),’VMM-YYYY’)
D . Date(InMontht[Common Date]),’MMM-YYYY’)

Reveal Solution Hide Solution

Question #32

A data architect needs to write the expression for a measure on a KPI to show the sales person with the highest sales. The sort order of the values of the fields is unknown. When two or more sales people have sold the same amount, the expression should return all of those sales people.

Which expression should the data architect use?

A)

B)

C)

D)

A . Option A
B . Option B
C . Option C
D . Option D

Reveal Solution Hide Solution

Question #33

Perform a Left Join Load on the Encounters table to the master calendar and alias the date fields appropriately for the Discharge Date

Reveal Solution Hide Solution

Question #33

Perform a Left Join Load on the Encounters table to the master calendar and alias the date fields appropriately for the Discharge Date

Reveal Solution Hide Solution

Question #35

Exhibit

Refer to the exhibit.

The salesperson ID and the office to which the salesperson belongs is stored for each transaction. The data model also contains the current office for the salesperson. The current office of the salesperson and the office the salesperson was in when the transaction occurred must be visible. The current source table view of the model is shown. A data architect must resolve the synthetic key.

How should the data architect proceed?

A . Comment out the Office in the Transaction table
B . Inner Join the Transaction table to the CurrentOffice table
C . Alias Office to CurrentOffice In the CurrentOffice table
D . Force concatenation between the tables

Reveal Solution Hide Solution

Correct Answer: C
C

Explanation:

In the provided data model, both the CurrentOffice and Transaction tables contain the fields SalesID and Office. This leads to the creation of a synthetic key in Qlik Sense because of the two common fields between the two tables. A synthetic key is created automatically by Qlik Sense when two or more tables have two or more fields in common. While synthetic keys can be useful in some scenarios, they often lead to unwanted and unexpected results, so it’s generally advisable to resolve them.

In this case, the goal is to have both the current office of the salesperson and the office where the transaction occurred visible in the data model.

Here’s how each option compares:

Option A: Comment out the Office in the Transaction table: This would remove the Office field from the Transaction table, which would prevent you from seeing which office the salesperson was in when the transaction occurred. This option does not meet the requirement.

Option B: Inner Join the Transaction table to the CurrentOffice table: Performing an inner join would merge the two tables based on the common SalesID and Office fields. However, this might result in a loss of data if there are sales records in the Transaction table that don’t have a corresponding record in the CurrentOffice table or vice versa. This approach might also lead to unexpected results in your analysis.

Option C: Alias Office to CurrentOffice In the CurrentOffice table: By renaming the Office field in the CurrentOffice table to CurrentOffice, you prevent the synthetic key from being created. This allows you to differentiate between the salesperson’s current office and the office where the transaction occurred. This approach maintains the integrity of your data and allows for clear analysis.

Option D: Force concatenation between the tables: Forcing concatenation would combine the rows of both tables into a single table. This would not solve the issue of distinguishing between the current office and the office at the time of the transaction, and it could lead to incorrect data associations.

Given these considerations, the best approach to resolve the synthetic key while fulfilling the requirement of having both the current office and the office at the time of the transaction visible is to Alias Office to CurrentOffice in the CurrentOffice table. This ensures that the data model will accurately represent both pieces of information without causing synthetic key issues.

Question #36

Refer to the exhibit.

A data architect needs to build a dashboard that displays the aggregated sates for each sales representative. All aggregations on the data must be performed in the script.

Which script should the data architect use to meet these requirements?

A)

B)

C)

D)

A . Option A
B . Option B
C . Option C
D . Option D

Reveal Solution Hide Solution

Correct Answer: C
C

Explanation:

The goal is to display the aggregated sales for each sales representative, with all aggregations being performed in the script. Option C is the correct choice because it performs the aggregation correctly using a Group by clause, ensuring that the sum of sales for each employee is calculated within the script.

Data Load:

The Data table is loaded first from the Sales table. This includes the OrderID, OrderDate, CustomerID, EmployeeID, and Sales.

Next, the Emp table is loaded containing EmployeeID and EmployeeName.

Joining Data:

A Left Join is performed between the Data table and the Emp table on EmployeeID, enriching the data with EmployeeName.

Aggregation:

The Summary table is created by loading the EmployeeName and calculating the total sales using the sum([Sales]) function.

The Resident keyword indicates that the data is pulled from the existing tables in memory, specifically the Data table.

The Group by clause ensures that the aggregation is performed correctly for each EmployeeName, summarizing the total sales for each employee.

Key Qlik Sense Data Architect

Reference: Resident Load: This is a method to reuse data that is already loaded into the app’s memory. By using a Resident load, you can create new tables or perform calculations like aggregation on the existing data.

Group by Clause: The Group by clause is essential when performing aggregations in the script. It groups the data by specified fields and performs the desired aggregation function (e.g., sum, count).

Left Join: Used to combine data from two tables. In this case, Left Join is used to enrich the sales data with employee names, ensuring that the sales data is associated correctly with the respective employee.

Conclusion: Option C is the most appropriate script for this task because it correctly performs the necessary joins and aggregations in the script. This ensures that the dashboard will display the correct aggregated sales per employee, meeting the data architect’s requirements.

Question #37

Exhibit.

A data architect must load the two tables without creating a synthetic key. The data architect also must make sure expressions like Sum([Budgeted Sales]) are calculated correctly.

Which load script meets these requirements?

A)

B)

C)

D)

A . Option A
B . Option B
C . Option C
D . Option D

Reveal Solution Hide Solution

Correct Answer: A
A

Explanation:

In the scenario provided, the data architect needs to load two tables (Budget and Sales) without creating a synthetic key, while ensuring that expressions like Sum([Budgeted Sales]) are calculated correctly.

Here is a breakdown of the options:

Option A (Outer Join): This option uses an outer join between the Sales table and the Budget table. While this approach will combine the tables based on the common fields (Year and Region), it will result in a single table that contains all fields from both tables. This approach prevents the creation of a synthetic key and retains all records from both tables, ensuring that all budgeted and actual sales data is available. As a result, calculations like Sum([Budgeted Sales]) will work correctly. This is the correct solution.

Option B (Concatenate): This option uses concatenate, which combines the tables by stacking them on top of each other as if they were one table. This approach will not prevent synthetic keys and could cause issues with calculations since Budgeted Sales and Actual Sales would be in the same column, leading to incorrect aggregation results.

Option C (Separate Load): This option simply loads the tables separately without any join or concatenation. While this will not create a synthetic key, it will result in two separate tables in the data model. Without any connection between these tables, calculations involving both Budgeted Sales and Actual Sales will not work correctly.

Option D (Inner Join): This option uses an inner join, which will combine only the records that match in both tables based on Year and Region. While this approach avoids synthetic keys, it may exclude records that do not have a corresponding match in both tables, potentially leading to incomplete data.

Given the requirements to avoid synthetic keys and ensure correct calculations, Option A (Outer Join) is the most appropriate approach. It ensures all relevant data is included and that the data model remains free from synthetic keys, while also allowing accurate calculations.

Question #37

Exhibit.

A data architect must load the two tables without creating a synthetic key. The data architect also must make sure expressions like Sum([Budgeted Sales]) are calculated correctly.

Which load script meets these requirements?

A)

B)

C)

D)

A . Option A
B . Option B
C . Option C
D . Option D

Reveal Solution Hide Solution

Correct Answer: A
A

Explanation:

In the scenario provided, the data architect needs to load two tables (Budget and Sales) without creating a synthetic key, while ensuring that expressions like Sum([Budgeted Sales]) are calculated correctly.

Here is a breakdown of the options:

Option A (Outer Join): This option uses an outer join between the Sales table and the Budget table. While this approach will combine the tables based on the common fields (Year and Region), it will result in a single table that contains all fields from both tables. This approach prevents the creation of a synthetic key and retains all records from both tables, ensuring that all budgeted and actual sales data is available. As a result, calculations like Sum([Budgeted Sales]) will work correctly. This is the correct solution.

Option B (Concatenate): This option uses concatenate, which combines the tables by stacking them on top of each other as if they were one table. This approach will not prevent synthetic keys and could cause issues with calculations since Budgeted Sales and Actual Sales would be in the same column, leading to incorrect aggregation results.

Option C (Separate Load): This option simply loads the tables separately without any join or concatenation. While this will not create a synthetic key, it will result in two separate tables in the data model. Without any connection between these tables, calculations involving both Budgeted Sales and Actual Sales will not work correctly.

Option D (Inner Join): This option uses an inner join, which will combine only the records that match in both tables based on Year and Region. While this approach avoids synthetic keys, it may exclude records that do not have a corresponding match in both tables, potentially leading to incomplete data.

Given the requirements to avoid synthetic keys and ensure correct calculations, Option A (Outer Join) is the most appropriate approach. It ensures all relevant data is included and that the data model remains free from synthetic keys, while also allowing accurate calculations.

Question #39

The data architect has been tasked with building a sales reporting application.

• Part way through the year, the company realigned the sales territories

• Sales reps need to track both their overall performance, and their performance in their current territory

• Regional managers need to track performance for their region based on the date of the sale transaction

• There is a data table from HR that contains the Sales Rep ID, the manager, the region, and the start and end dates for that assignment

• Sales transactions have the salesperson in them, but not the manager or region.

What is the first step the data architect should take to build this data model to accurately reflect performance?

A . Implement an "as of calendar against the sales table and use ApplyMap to fill in the needed management data
B . Create a link table with a compound key of Sales Rep / Transaction Date to find the correct manager and region
C . Use the IntervalMatch function with the transaction date and the HR table to generate point in time data
D . Build a star schema around the sales table, and use the Hierarchy function to join the HR data to the model

Reveal Solution Hide Solution

Question #39

The data architect has been tasked with building a sales reporting application.

• Part way through the year, the company realigned the sales territories

• Sales reps need to track both their overall performance, and their performance in their current territory

• Regional managers need to track performance for their region based on the date of the sale transaction

• There is a data table from HR that contains the Sales Rep ID, the manager, the region, and the start and end dates for that assignment

• Sales transactions have the salesperson in them, but not the manager or region.

What is the first step the data architect should take to build this data model to accurately reflect performance?

A . Implement an "as of calendar against the sales table and use ApplyMap to fill in the needed management data
B . Create a link table with a compound key of Sales Rep / Transaction Date to find the correct manager and region
C . Use the IntervalMatch function with the transaction date and the HR table to generate point in time data
D . Build a star schema around the sales table, and use the Hierarchy function to join the HR data to the model

Reveal Solution Hide Solution