Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
gauthamchilled
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
michielvandegoo
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)))

));

gauthamchilled
Creator
Creator

Hi Michiel,

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

gauthamchilled
Creator
Creator

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

Revenue

Consolidatedcost

Profit

tharanikannan
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.

gauthamchilled
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

tharanikannan
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;

gauthamchilled
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

tharanikannan
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.