Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How can i do this calculation at the time of loading(with scripting) withiut using variable? help me out

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

11 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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?

saumyashah90
Specialist
Specialist

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

Not applicable
Author

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

preminqlik
Specialist II
Specialist II

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;

Not applicable
Author

my table is in qvd files,will this code work on that too?

preminqlik
Specialist II
Specialist II

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;

Not applicable
Author

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;

Not applicable
Author

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;