hello Everyone my code is not working,can anyone give the possible solution?

[#1]

hello Everyone

my code is not working,can anyone give the possible solution,where I'm doing mistake

tab1:

LOAD int_fk_dim_business_owner as  pk_dim_business_owner,

     int_fk_dim_channel,

     int_fk_dim_entity as pk_dim_entity ,

     int_fk_dim_geo as  pk_dim_geo ,

     int_fk_dim_industry as  pk_dim_industry,

     int_fk_dim_market,

     int_fk_dim_product,

     int_fk_dim_pov as  pk_dim_pov,

     int_fk_dim_period as pk_dim_period,

     int_fk_dim_uom as pk_dim_uom,

     int_fk_data_detail as pk_data_details,

     money_measure,

     int_fk_load_id,

     version_no,

     le_version_no,

     int_fk_dim_pov_2

FROM

E:\Satya\qvd\FACT_FINANCIALS.qvd

(qvd);

Concatenate(tab1)

LOAD

     pk_data_details,

     int_heirarchy_level,

     str_coa_code,

     str_partent_coa_code,

     str_coa_description,

     str_display_description,

     int_display_order,

     str_calculation_logic,

     str_account_type,

     int_transaction_type

FROM

E:\Satya\qvd\DIM_DATA_DETAILS.qvd

(qvd);

HierarchyBelongsTo( str_coa_code,str_partent_coa_code,node_name,ancestor_id,ancestor_name)

LOAD

str_partent_coa_code,

pk_data_details,

str_coa_description as node_name,

str_coa_code,

sum(money_measure) as A

Resident tab1

Where ancestor_id='BS020030040010000000000000000000'

Group by pk_data_details,str_coa_code,ancestor_id,str_partent_coa_code,str_coa_description;

[#2]

Hello,

Group by and where clauses on the same iteracion usually gives error.

Try to apply them within precedent load.

Something like

tab11

load * where whatever;

load dim1,dim2,dim3....

          sum(MESS1) as summess1

sum(mess2,) as summess2...

resident tab1 group by dim1,dim2,dim3.

Please take care as well to group by all the dims that are on the table that is aggregatting.

Hope it helps

SABR

[jagan]

Hi,

There is a issue in in the below "ancestor_id " is not in the Load  statement.

HierarchyBelongsTo( str_coa_code,str_partent_coa_code,node_name,ancestor_id,ancestor_name)

LOAD

str_partent_coa_code,

pk_data_details,

str_coa_description as node_name,

str_coa_code,

ancestor_id,

sum(money_measure) as A

Resident tab1

Where ancestor_id='BS020030040010000000000000000000'

Group by pk_data_details,str_coa_code,ancestor_id,str_partent_coa_code,str_coa_description;

Regards,

Jagan.

[#4]

hello Jagan

after applying your code its showing this error

Field not found - <ancestor_id>

HierarchyBelongsTo( str_coa_code,str_partent_coa_code,node_name,ancestor_id,ancestor_name)

LOAD

str_partent_coa_code,

pk_data_details,

str_coa_description as node_name,

str_coa_code,

ancestor_id,

sum(money_measure) as A

Resident tab1

Where ancestor_id='BS020030040010000000000000000000'

Group by pk_data_details,str_coa_code,ancestor_id,str_partent_coa_code,str_coa_description