Creating a Table

[santhoo_san]

Hi,

I have data as below:

Date	Center	Particular	Amount
01/01/2012	1	Revenue	150
01/01/2012	1	Revenue	100
01/01/2012	1	Revenue	225
01/01/2012	1	Cost	50
01/01/2012	1	Cost	75
01/01/2012	2	Revenue	80
01/01/2012	2	Revenue	30
01/01/2012	2	Cost	25
01/01/2012	2	Cost	15

with this data, I want to create / derive a table which should look as below:

Date	Center	Particular	Amount
01/01/2012	1	Revenue	150
01/01/2012	1	Revenue	100
01/01/2012	1	Revenue	225
01/01/2012	1	Cost	50
01/01/2012	1	Cost	75
01/01/2012	1	Margin	350
01/01/2012	2	Revenue	80
01/01/2012	2	Revenue	30
01/01/2012	2	Cost	25
01/01/2012	2	Cost	15
01/01/2012	2	Margin	70

Margin is calculated as Revenue minus Cost for a particular Center for a particular date.

Can some one help me in writing the script.

Thank you,

Santhosh

[swuehl · Accepted solution]

Maybe like this?

TABLE:

LOAD Date,

     Center,

     Particular,

     Amount

FROM

[http://community.qlik.com/thread/62328?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

TMP:

LOAD Date, Center, sum(Amount) as Revenue

Resident TABLE where Particular = 'Revenue' group by Center, Date;

Concatenate LOAD Date, Center, sum(Amount) as Cost

Resident TABLE where Particular = 'Cost' group by Center, Date;

Concatenate (TABLE)

LOAD Date, Center, sum(Revenue) - sum(Cost) as Amount, 'Margin' as Particular

Resident TMP group by Center, Date;

drop table TMP;

[santhoo_san]

Thank you Swuehl. It is working wonderfully.

Regards,

Santhosh