topic Re: derived column in script in QlikView

derived column in script

gauthamchilled — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: derived column in script

Michiel_QV_Fan — Wed, 07 Nov 2018 14:54:26 GMT

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

));

Re: derived column in script

gauthamchilled — Thu, 08 Nov 2018 01:37:38 GMT

Hi Michiel,

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

Re: derived column in script

gauthamchilled — Thu, 08 Nov 2018 01:51:17 GMT

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

Revenue

Consolidatedcost

Profit

Re: derived column in script

tharanikannan — Thu, 08 Nov 2018 04:05:31 GMT

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.

Re: derived column in script

gauthamchilled — Thu, 08 Nov 2018 06:10:35 GMT

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

Re: derived column in script

tharanikannan — Thu, 08 Nov 2018 06:24:36 GMT

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;

Re: derived column in script

gauthamchilled — Thu, 08 Nov 2018 06:50:05 GMT

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

Re: derived column in script

tharanikannan — Thu, 08 Nov 2018 07:09:37 GMT

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.