Discussion Board for collaboration on QlikView Scripting.
I have a below data table:
I want expected table as below:
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.
Go to Solution.
LOAD * INLINE [
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
load 'Telephone Cost' as Type,
sum(if(Type='Telephone Expenditure', Value, if(Type='Telephone Profit',-Value,0 ))) as Value
See attached qvw
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)
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.
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.