derived column in script

[gauthamchilled]

Hi,

Need a help in calculating a derived column in the script. My current column values are like this

Test:

Load * inline

[

Hier1,Amt

Noint,100

Bizcost,120

Devcost,340

IntCost,500

];

I need to create two more fields like, if  Hier1=Noint, then it should be Revenue,

Consolidatecost=Bizcost+Devcost,IntCost

Profit=Revenue+Bizcost+Devcost+Intcost

so my Hier1 should appear with additional fields like below

Hier1

Noint

Bizcost

Devcost

IntCost

Revenue

Consolidatedcost

Profit

Can anyone help on how to achiveve these derived columns, so i need to do on the fly calc and burden performance?

-Gautham

[Michiel_QV_Fan]

Well with the use of an QVD you could do this like this:

table:

Load * inline

[

Hier1,Amt

Noint,100

Bizcost,120

Devcost,340

IntCost,500

];

STORE table into table.qvd (qvd);

table2:

Concatenate (table)

LOAD 'ConsolidateCost' as Hier1,

Bizcost+Devcost+IntCost as Amt;

LOAD @1 as Noint,

     @2 as Bizcost,

     @3 as Devcost,

     @4 as IntCost

FROM

[table.qvd]

(qvd, filters(

Transpose(),

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)))

));

[gauthamchilled]

Hi Michiel,

Is there any other way without using a QVD? may be with resident table? or preceding load?

[gauthamchilled]

Also the solution or the script is not populating the three new fields

Revenue

Consolidatedcost

Profit

[tharanikannan]

Hi,

Please find the script below,

Test:

Load * inline

[

Hier1,Amt

Noint,100

Bizcost,120

Devcost,340

IntCost,500

];

Table1:

load

*,

if(Hier1='Noint',Amt ) as Revenue

Resident Test;

drop table Test;

Please provide proper condition for the rest.

Thanks

Tharani.

[gauthamchilled]

i need three calculated columns

conditions are below

if  Hier1=Noint, then it should be Revenue,

Consolidatecost=Bizcost+Devcost,IntCost

Profit=Revenue+Bizcost+Devcost+Intcost

[tharanikannan]

Hi,

Please check the script below, But still i am not clear about your Consolidatecost  and Profit

Test:

Load * inline

[

Hier1,Amt

Noint,100

Bizcost,120

Devcost,340

IntCost,500

];

Table1:

load

*,

if(Hier1='Noint',Amt ) as Revenue,

if(Hier1='Bizcost',Amt) as Bizcost1,

if(Hier1='Devcost',Amt) as Devcost1,

if(Hier1='IntCost',Amt) as IntCost1

Resident Test;

drop table Test;

Table2:

load*,

//Revenue,

Bizcost1+Devcost1+IntCost1 as Consolidatecost,

Revenue+Bizcost1+Devcost1+IntCost1 as Profit

Resident Table1;

drop table Table1;

[gauthamchilled]

Hi Tharani,

Current script is

Load * inline

[

Hier1,Amt

Noint,100

Bizcost,120

Devcost,340

IntCost,500

];

i just need three more rows under Hier1

Hier1

Noint

Bizcost

Devcost

IntCost

Revenue

Consolidatedcost

Profit

Consolidatedcost is sum of Bizcost+Devcost,IntCost

prifit is sum of Revenue+Bizcost+Devcost+Intcost

[tharanikannan]

Hi,

I understand Your requirements very well,

We cant add Bizcost+Devcost,IntCost ,

We should get the Amt value for each of this under some condition and then add.

Please provide ur qvw or clarify on your conditions.

Thanks,

Tharani.