Optimized solution

[anushahegde]

Hi all,

Could someone help me to get the answers for the below questions? I also want to learn the reasons for the answer..

1) Which is the highest performing expression to accurately count the number of Account Owners?

1. COUNT (%AccountOwnerId)
2. COUNT(AccountOwnerld)
3. COUNT(DISTINCT %AccountOwnerId)
4. COUNT(TOTAL %AccountOwnerId)

2) A business analyst needs to sort the values for the Product dimension explicitly as "Planes", then "Trains", then "Automobiles" The analyst needs a highly optimized solution to provide the best performance for an application with between 900 million and 1 billion records Which solution should the analyst use?

1. Use the Ord function in the user interface to sort in a custom order
2. Build nested IF statements in the user interface to assign numeric values, then sort numerically
3. Create a new calculated dimension in the Ul named ProductSort, then sort the Product field by the new ProductSort field
4. Assign a numeric value to the Product values using the DUAL function during the data load, then sort numerically in the Ul

3) The straight table does NOT show all rows from the Field1 list box What does this indicate about the dataset?

1. Field2 is a synthetic key
2. A synthetic key is required
3. Field2 contains null values
4. Field2 contains blank values

4) Two of the tables in the QlikView data model are Invoice Header (one million rows) and Invoice Detail (nine million rows) InvoicelD links the two Invoice tables.

This expression is used several times in the QlikView application, count (distinct invoiceiT) How can a business analyst optimize this expression for use in the application?

1. In the load script, sort the InvoiceDetail table by InvoicelD. Add if (Peek(invoiceiD)=invoiceiD, 0,1) as DistinctlnvoicelD. Change the expression to Sum(DistinctinvoiceiD)
2. In the load script, add I as DistinctlnvoicelD to the InvoiceDetail table. Change the expression to Sum(DistinctlnvoicelD)
3. In the load script, sort the InvoiceDetail table by InvoicelD. Add if (peek(invoiceiD)=invoiceiD, 1,0) as DistinctlnvoicelD. Change the expression to Sum (DistinctlnvoicelD)
4. In the load script, add l as DistinctlnvoicelD to the InvoiceHeader table Change the expression to Sum(DistinctlnvoicelD)

5) A data model contains 1 billion records and a straight table renders all of the data at the most granular level. The business users do NOT need all of the detailed information. The business analyst needs to improve the rendering time of the straight table.

Which two approaches should the analyst use? (Select two )

1. Update the straight table to a more summarized view
2. Replace all table fields with only one field which concatenates the values of all previous table fields
3. Remove all master dimensions and master measures, and replace with distinct expressions in each visualization
4. Specify a specific sort order in the output of the straight table using a field not found in the straight table columns.
5. Set up a calculation condition to only show the straight table if filtered down to a much smaller number of rows (e.g., 100k rows) 

[Brett_Bleess]

Anusha, I would recommend looking around in the Design Blog area, I am going to give you a couple/few direct links to some things I think may be directly related, and then the last link will be to the top level of the Design Blog area, you can use that one to search yourself for further posts that may help.  There are 700+ posts in this area now from our experts and most are how-to related.

https://community.qlik.com/t5/Qlik-Design-Blog/Count-or-Count-distinct/ba-p/1469730

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/Use-Aggregation-Functions/ba-p/1475833

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regarding the straight table, you likely need to use Sum of Rows option there instead of Expression, as the latter will collapse duplicate rows.  The other option is to use RowNo() or RecNo() in your load to ensure every row is unique...

I think this should get you started and on the right track, hopefully it helps.

Regards,
Brett