Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

derived column in script

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

8 Replies
Highlighted
Specialist
Specialist

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)))

));

Highlighted
Creator
Creator

Hi Michiel,

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

Highlighted
Creator
Creator

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

Revenue

Consolidatedcost

Profit

Highlighted
Contributor III
Contributor III

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.

Highlighted
Creator
Creator

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

Highlighted
Contributor III
Contributor III

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;

Highlighted
Creator
Creator

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

Highlighted
Contributor III
Contributor III

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.