topic Detail and Summary Data in QlikView

Detail and Summary Data

Mon, 24 Jan 2011 20:04:35 GMT

I have a detail table as well as a summary table(s) I want to load into Qlikview. The issue I am having is the summary table will like to multiple fields within the detail table causing a synthetic key. How can I avoid this situation. I want to be able to bring in the summary data, so I can have better performance.

Detail and Summary Data

Anonymous — Mon, 24 Jan 2011 20:08:19 GMT

Synthetic keys will be generated when fields have the same names, just rename on of the fields.

Load
fieldname as newfieldname

Detail and Summary Data

Mon, 24 Jan 2011 20:14:13 GMT

Right but then how do I have a listbox control both tables?

Detail and Summary Data

Anonymous — Mon, 24 Jan 2011 20:16:22 GMT

Please share the script you have now, then I will try to help you adjust it.

Detail and Summary Data

Mon, 24 Jan 2011 20:23:34 GMT

I have not run the script yet because I know it will cause an issue with my data.

Detail and Summary Data

Anonymous — Mon, 24 Jan 2011 20:26:28 GMT

I don't know why but I get "Access denied" on this document.
Can you copy your script here?

Detail and Summary Data

Mon, 24 Jan 2011 20:37:50 GMT

LOAD

EXTERNAL_PRJ_MGE_SYSTEM_PRJ_ID

,ENGINEERING_PROJECT_NM

,ENGR_PROJECT_CATEGORY_CDE

,ISO_CURRENCY_CDE

,PROJECT_OWNER_REGION_CDE

,PROJECT_MGE_NETWORK_USER_ID

,PROD_MANAGER_NETWORK_USER_ID

,PLANNED_PROJECT_START_DT

,REVISED_PROJECT_START_DT

,ACTUAL_PROJECT_START_DT

,PLANNED_CONCEPT_COMPLETION_DT

,REVISED_CONCEPT_COMPLETION_DT

,ACTUAL_CONCEPT_COMPLETION_DT

,PLANNED_DESIGN_COMPLETION_DT

,REVISED_DESIGN_COMPLETION_DT

,ACTUAL_DESIGN_COMPLETION_DT

,PLANNED_TOOL_COMPLETION_DT

,REVISED_TOOL_COMPLETION_DT

,ACTUAL_TOOL_COMPLETION_DT

,PLANNED_PROJECT_COMPLETION_DT

,REVISED_PROJECT_COMPLETION_DT

,ACTUAL_PROJECT_COMPLETION_DT

,PROJECT_STATUS_CDE

,ENGINEERING_PRJ_STATUS_DESC

,PLANNED_DEFINITION_CPLT_DT

,REVISED_DEFINITION_CPLT_DT

,ACTUAL_DEFINITION_CPLT_DT

,PLANNED_INDUSTRIALIZE_CPLT_DT

,REVISED_INDUSTRIALIZE_CPLT_DT

,ACTUAL_INDUSTRIALIZE_CPLT_DT

,PLANNED_EVALUATION_CPLT_DT

,REVISED_EVALUATION_CPLT_DT

,ACTUAL_EVALUATION_CPLT_DT

,PLANNED_LAUNCH_DT

,PROJECT_INDUSTRY_CDE

,INDUSTRY_NAME

,PRJ_COMPETENCY_BUSINESS_CDE

,PROJECT_PRIORITY_CDE

,HIGH_VOL_MFR_CITY_LOCATION_TXT

,TOTAL_USD_SALES_FORECAST_AMT

,AVERAGE_STANDARD_MARGIN_PCT

,GBL_METALS_SELECTION_SYS_IND

,ZERO_PROPRIETARY_MATERIALS_IND

,TOTAL_NUMBER_OF_TOOLS_QTY

,TOTAL_NBR_TOOLS_3D_DSGN_QTY

,TOTAL_NUMBER_DESIGN_REUSE_QTY

,TOTAL_NBR_STAGE_GATE_REVW_QTY

,TOTAL_STAGE_GATE_TURNBACKS_QTY

,TOTAL_USD_CAPITAL_BUDGET_AMT

,TOTAL_USD_EXPENSE_BUDGET_AMT

,RSPB_DSGN_GRP_UNIT_ID as RDG_ID

,PROJECT_APPROVAL_CMTE_SHORT_NM

,PROJECT_COMPLEXITY_CDE

,PROJECT_COMPLEXITY_DESC

,PROJECT_COMPLEXITY_FULLDESC

,PROJECT_LEVERAGE_CDE

,TECHNOLOGY_NEWNESS_CDE

,MARKET_NEWNESS_CDE

,STRATEGIC_IMPR_SCORE_FCTR

,COMPETITION_SCORE_FCTR

,PRODUCT_DIFF_SCORE_FCTR

,MKT_ATTRACTIVENESS_SCORE_FCTR

,COMPETENCY_SCORE_FCTR

,DESIGN_COMPLEXITY_SCORE_FCTR

,MFR_COMPLEXITY_SCORE_FCTR

,LIFETIME_REVENUE_SCORE_FCTR

,FINANCIAL_REWARD_SCORE_FCTR

,CUSTOMER_BASE_SCORE_FCTR

,MARKET_TIMING_SCORE_FCTR

,INVENTIVENESS_SCORE_FCTR

,WT_PRJ_ATTRACTIVE_SCORE_FCTR

,COMPONENT_PARTS_REUSED_QTY

,MGE_PROFIT_CENTER_ABBR_NM

,MGE_PROFIT_CENTER_DESC

,PROFIT_CENTER_GROUP_ID

,MGE_PROFIT_CENTER_ABBR_DESC

,SBMT_ENGR_PRJ_CUR_PHASE_TXT

,LEVEL_OF_CUST_INVOLVEMENT_CDE

,PRJ_STRATEGIC_INITIATIVE_CDE

,AVG_STANDARD_MARGIN_10_YR_PCT

,OPERATING_INCOME_5_YR_PCT

,OPERATING_INCOME_10_YR_PCT

,NET_PRESENT_VALUE_5_YR_AMT

,NET_PRESENT_VALUE_10_YR_AMT

,TOTAL_OVERALL_SALES_5_YR_AMT

,TOTAL_OVERALL_SALES_10_YR_AMT

,INTERNAL_RATE_OF_RTN_5_YR_PCT

,INTERNAL_RATE_OF_RTN_10_YR_PCT

,RTN_ON_ENGINEERING_5_YR_PCT

,RTN_ON_ENGINEERING_10_YR_PCT

,RTN_INVESTED_CAPL_5_YR_PCT

,RTN_INVESTED_CAPL_10_YR_PCT

,PAYBACK_NUMBER_OF_YEARS_QTY

,AVOID_PRPY_MATL_EXHAUSTED_IND

,CHF_ENGR_SGN_OFF_PRPY_MATL_IND

,ENGR_PRJ_CURRENT_PHASE_TXT

,LEANPD_COMPLIANT_IND

,TIME_TO_MARKET_DRTN_DAY_QTY

,TIME_TO_VOLUME_DRTN_DAY_QTY

,TIME_TO_VALID_DRTN_DAY_QTY

,CONCEPT_DRTN_DAY_QTY

,DESIGN_DRTN_DAY_QTY

,VALIDATION_DRTN_DAY_QTY

,INDUSTRIALIZE_DRTN_DAY_QTY

,PLATFORM_REUSE_IND

,PRODUCT_LINE_CDE

,INTERVIEW_GUIDE_USED_IND

,KJ_ANALYSIS_PERFORMED_IND

,QFD1_CREATED_FOR_PROJECT_IND

,IFR_APPLIED_AT_EXEC_OF_PRJ_IND

,PRCS_MAP_MADE_FOR_MFR_PRCS_IND

,POST_PRJ_ANLS_AT_GATE5_PH_IND

,FIN_ANLS_PRFM_AT_GATE6_PH_IND

,INVEST_TO_WIN_CDE

,PROJECT_SEGMENT_CDE

,PRJ_DATA_TAG_FREE_FORM_TXT

,RSPB_DESIGN_GROUP_DESC_LABEL

,FIVEYRSALESFORCAST

,GROWTHENGINEERINGCOST

FROM

(qvd)

WHERE

MATCH

(ENGR_PROJECT_CATEGORY_CDE,'0','6');

SummaryTable:

select

dd.period_end_dt

eps.rspb_dsgn_grp_unit_id as

RDG_ID

eps.project_industry_cde

eps.project_owner_region_cde

eps.project_complexity_cde as

PROJECT_COMPLEXITY_CDE

sum(eps.time_to_volume_drtn_day_qty)

count(*) as TotalCount

round(round(sum(eps.time_to_volume_drtn_day_qty),1)/count(*),1)

from

engineering_projects_sv eps

date_dmn dd

where

(dd.tyco_period_end_dt < sysdate and dd.tyco_period_end_dt > sysdate-365)

and

dd.date_level_desc='MONTH'

and

eps.project_status_label in ('COMPLETED (4)','CLOSED (5)')

and

eps.actual_industrialize_cplt_dt between dd.tyco_period_end_dt - 365 and dd.tyco_period_end_dt

and

eps.project_complexity_cde in (0, 1, 2)

and

eps.engr_project_category_cde='0'

group

dd.tyco_period_end_dt

eps.rspb_dsgn_grp_unit_id

eps.project_industry_cde

eps.project_owner_region_cde

eps.project_complexity_cde

order

by 1, 2, 3, 4, 5, 6, 7;

Detail and Summary Data

Anonymous — Mon, 24 Jan 2011 20:47:16 GMT

Sorry that is not useful with out the files.

Can you show me your table viewer? A print screen would be fine.

Detail and Summary Data

Mon, 24 Jan 2011 20:56:15 GMT

See attached.

Detail and Summary Data

pover — Mon, 24 Jan 2011 21:31:14 GMT

Rename the fields between the 2 tables in order to avoid the synthetic table and to link the 2 tables create a composite key field. For example,

PROJECT_OWNER_REGION_CDE & '_' & PROJECT_INDUSTRY_CDE & '_' & RDG_ID & '_' & PROJECT_COMPLEXITY_CDE as Summary_Detail_Table_Key

Regards.

Detail and Summary Data

johnw — Mon, 24 Jan 2011 21:57:20 GMT


guytanguay wrote:I have a detail table as well as a summary table(s) I want to load into Qlikview. The issue I am having is the summary table will like to multiple fields within the detail table causing a synthetic key. How can I avoid this situation. I want to be able to bring in the summary data, so I can have better performance.

I'd only load summary data as a last resort. I'd try just about anything else first to improve performance. That said, I also don't see anything specifically wrong with your summary table or synthetic key. It seems like a natural consequence of having both summary and detail data, and I wouldn't expect it to cause problems. Are you seeing problems?

Detail and Summary Data

Tue, 25 Jan 2011 16:15:44 GMT

This has helped me with the summary table however it has caused another problem with loosely coupled keys. I need my summary table to work off my master calendar as well as my project data. However, when I name my summary date field the same as my master calendar table I get loosely coupled key, and no data shows for my summary in my graph.

What am I doing wrong here?

Detail and Summary Data

pover — Tue, 25 Jan 2011 18:14:59 GMT

You are creating a loop in the data model that you need to eliminate.

First, let me say that I agree with John that the ideal solution would be just to use the detailed information and dynamically sum information in the reports.

Your problems could be fixed if you concatenate the Summary Table with the Detail Table where the corresponding columns between the 2 tables have the same name (Remove the composite key). You might also need to add an additional column that acts as a flag that tells you if the row is summary or detail (eg. 0 = summary and 1 = detail)

However, this solution may greatly increase the size of the file in RAM because alot of columns are going to be filled with null values and null values appear to take up some space.

If the data volume is high then you might think about creating 2 QV applications: 1 for summary data and the other of detail data. You can link the 2 applications with a button/action that will transfer the current selection when the user navigates to the other application making it feel like the same application.

Regards.

Detail and Summary Data

Tue, 25 Jan 2011 19:35:24 GMT

Putting the summary data in the detail table with concatinate does not fix my problem because of the date field. This is where I am getting tripped up and that is the cause of my looping of the data model.

Detail and Summary Data

Tue, 25 Jan 2011 19:50:18 GMT

Can you give me an example of this being done? I like the idea. I am having a very difficult time implementing the aggregation of data within an expression.

Detail and Summary Data

pover — Wed, 26 Jan 2011 01:43:05 GMT

The concatenate function will work since it makes 2 tables, 1 table.

Otherwise I've attached an example of linking to QV Apps where the current selections of the first QV App are transferred to the other QV App.

Regards.