Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)))
));
Hi Michiel,
Is there any other way without using a QVD? may be with resident table? or preceding load?
Also the solution or the script is not populating the three new fields
Revenue
Consolidatedcost
Profit
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.
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
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;
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
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.