Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone
vart1 = sum{<ancestor_id={'BS020030040010000000000000000000'},str_pov_name={'ACTUAL'}>}money_measure)
here var1 is the calculated value of money measure where ancestor_id={'BS020030040010000000000000000000'} and
str_pov_name={'ACTUAL'} both field are from different table
How can i do this calculation at the time of loading(with scripting) withiut using variable? help me out
It's possible, but I suggest you don't try. You will first have to join the two tables into one and then create another table to store the sum using the data from joined table.
Thanks for reply,but I exactly want these data as you explained
can you please explain how can i do this,should i have to use concat?
what is the unique field in both?
If possibe try to give information of all fields from the tables so that we can advice you
pk_dim_entity is the unique field on both,i want to do exact what Wassenaar suggested,can you help me out with the scripting
hey kumar, try this
tab:
load ancestor_id,
str_pov_name,
sum(money_measure)
from file.xls where ancestor_id='BS020030040010000000000000000000' and str_pov_name='ACTUAL' group by ancestor_id,str_pov_name;
my table is in qvd files,will this code work on that too?
tab:
load ancestor_id,
str_pov_name,
sum(money_measure)
from file.qvd where ancestor_id='BS020030040010000000000000000000' and str_pov_name='ACTUAL' group by ancestor_id,str_pov_name;
Step1
Table1:
Load
pk_dim_entity,
ancestor_id,
money_measure
from tab1.qvd ;
join
Load
pk_dim_entity,
str_pov_name
from tab2.qvd;
Step2
[Consolidated Table]:
load
pk_dim_entity as PK_Dim_Entity,
ancestor_id as [Ancestor ID],
str_pov_name,
sum(money_measure)
Resident Table1
Where ancestor_id=’ BS020030040010000000000000000000’ and str_pov_name=’ ACTUAL’
Group by pk_dim_entity, ancestor_id, str_pov_name ;
Drop Table Table1;
Hello Rechard
I'am having this code but with that i have to use hirarichybelongsto function,but its not working
can you explain where I'am doing mistake
here is my code
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);
join
HierarchyBelongsTo( str_coa_code,str_partent_coa_code,node_name,ancestor_id,ancestor_name)
LOAD
str_coa_code,
str_partent_coa_code ,
str_coa_description as node_name,
pk_data_details
FROM
E:\Satya\qvd\DIM_DATA_DETAILS.qvd
(qvd);
LOAD
pk_data_details as PK_DATA_DETAILS,
ancestor_id as ANCESTOR_ID,
str_coa_code,
sum(money_measure)
Resident tab1
Where ancestor_id=' BS020030040010000000000000000000'
Group by pk_data_details;