Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

cabhijit
Valued Contributor

Row wise calculations

I have a below data table:

TypeValue
Room Expenditure700
Telephone Expenditure500
Room Profit400
Telephone Profit100

I want expected table as below:

TypeValue
Room Expenditure700
Telephone Expenditure500
Room Cost300
Telephone Cost400
Room Profit400
Telephone Profit100

Room Cost = Room Expenditure- Room Profit

same for telephone and other Types present in the table.

How can I calculate and generate these new rows in script?

Any help greatly appreciated.

Tags (1)
1 Solution

Accepted Solutions

Re: Row wise calculations

Try:

T1:

LOAD * INLINE [

    Type, Value

    Room Expenditure, 700

    Telephone Expenditure, 500

    Room Profit, 400

    Telephone Profit, 100

];

load 'Room Cost' as Type,

sum(if(Type='Room Expenditure', Value, if(Type='Room Profit',-Value,0 ))) as Value

resident T1;

load 'Telephone Cost' as Type,

sum(if(Type='Telephone Expenditure', Value, if(Type='Telephone Profit',-Value,0 ))) as Value

resident T1;

See attached qvw


talk is cheap, supply exceeds demand
4 Replies
Not applicable

Re: Row wise calculations

Hi Abhijit,

Do you have any field to group the different types? Or just have to compare each name?

Are you sure that this output wouldn´t be better for your document? (All the fact tables in the same row)

TypeCostExpenditureProfit
Room200350150
Telephone300440140

Re: Row wise calculations

Try:

T1:

LOAD * INLINE [

    Type, Value

    Room Expenditure, 700

    Telephone Expenditure, 500

    Room Profit, 400

    Telephone Profit, 100

];

load 'Room Cost' as Type,

sum(if(Type='Room Expenditure', Value, if(Type='Room Profit',-Value,0 ))) as Value

resident T1;

load 'Telephone Cost' as Type,

sum(if(Type='Telephone Expenditure', Value, if(Type='Telephone Profit',-Value,0 ))) as Value

resident T1;

See attached qvw


talk is cheap, supply exceeds demand
cabhijit
Valued Contributor

Re: Row wise calculations

Thank you for reply.

This works correctly, but i need to hardcore for all Types.

There are other Types present in the table other than Room and Telephone.

@ IOSU GARCIA

Right now I don't have any field to group different types.

Not applicable

Re: Row wise calculations

If you don't have any field to group all the different types, you need one. Try creating a new field with this:

SubField(Type, ' ', 1) as NewType,     // For differenciating Telephone, room,...

SubField(Type, ' ', 2) as DataType     // For differenciating Expenses, cost...

Once you have this new fields, it will be easy to achieve what you want, just following what Wassennar posted.

Community Browser