Detail and Summary Data

[#1]

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.

[Anonymous]

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

Load
fieldname as newfieldname

[#3]

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

[Anonymous]

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

[#5]

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

[Anonymous]

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

[#7]

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

by

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;

[Anonymous]

Sorry that is not useful with out the files.

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

[#9]

See attached.

[pover]

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.