where clause in resident .

[suvechha_b]

Temp: 

Load 

[product_key] ,

[period_key] ,

sale_units as Qty              

Resident FactInventory; 

Inner Join

Load 

product_key , 

[Std Cost Price] as Val              

Resident DimProduct;

Inner Join

Load

[period_key]

Resident DimPeriod;

OriginalTable:

LOAD

Qty,

Qty * Val As Vs

Resident Temp;

drop table OriginalTable;

I want to multiply the qty and value of a particular product_key ? please any one suggest

[v_iyyappan]

Hi

Try like this,

OriginalTable:

LOAD

[period_key],

Qty,

Sum(Qty * Val) As Vs

Resident Temp

group by [period_key],Qty;

drop table temp;

If u not get, please provide sample application.

Regards,

[Anonymous]

Hi,

What is the aim of your last
Inner Join
Load
[period_key]
Resident DimPeriod;

???

I don't really understand what you are trying to do but I think you should do it like this:

Temp: 
Load
[product_key] ,
[period_key] ,
sale_units   as Qty              
Resident FactInventory; 

left Join

(Temp)
Load 
product_key , 
[Std Cost Price]  as Val              
Resident DimProduct;


FinalTable:
LOAD
*,
Qty * Val   as Vs
Resident Temp;

DROP TABLE Temp;

[suvechha_b]

As my table have only one product_key and only one [Std Cost Price] but many sale_units against many period_key

I want the multiplication of sale_units * [Std Cost Price] against the product_key and against all period_key

[trappersw]

Temp1:

Load

[product_key] ,

[period_key] ,

sale_units as Qty             

Resident FactInventory;

Inner Join

Load

product_key ,

[Std Cost Price] as Val             

Resident DimProduct;

Inner Join

Load

[period_key]

Resident DimPeriod;

Temp:

Load

[product_key]  &'!' & [period_key]  as ID,

*

Resident Temp1;

drop table Temp1;

OriginalTable:

LOAD

ID,

Qty * Val As Vs

Resident Temp

group by ID;

drop table OriginalTable;

drop field ID;

[#6]

PFA !!!

[Anonymous]

This should do what you are expecting:

Temp: 
Load
[product_key] ,
[period_key] ,
sale_units   as Qty              
Resident FactInventory; 

left Join

(Temp)
Load 
product_key , 
[Std Cost Price]  as Val              
Resident DimProduct;

FinalTable:
LOAD
*,
Qty * Val   as Vs
Resident Temp;

DROP TABLE Temp;