topic Re: row minus row? in QlikView

row minus row?

Mon, 03 Mar 2014 05:51:40 GMT

I have two tables with same structure

I want 1st row of 1st table to be subtract from 1st row of second table

then 2nd row of 1st table to be subtracted from 2nd row of second table

and so on...

thanks in advance

Re: row minus row?

chiru_thota — Mon, 03 Mar 2014 06:07:12 GMT

Can you upload a sample ?

Re: row minus row?

CELAMBARASAN — Mon, 03 Mar 2014 06:28:59 GMT

Table you meant in presentation or in script?

Its better, if you could upload sample file.

Re: row minus row?

Anonymous — Mon, 03 Mar 2014 06:31:24 GMT

PFA, if it helps you

Re: row minus row?

MK_QSL — Mon, 03 Mar 2014 06:59:22 GMT

hope this helps...

Re: row minus row?

Mon, 03 Mar 2014 09:04:49 GMT

These are two sample files that i have attached. I want to subtract data of table1 from table22.

Re: row minus row?

Mon, 03 Mar 2014 09:37:03 GMT

This is my script.

tab1:

LOAD Particulars as Code1,

F2 as [Name of Entity],

[Gross Service Revenue billed to third party customers],

[Third Party Network costs],

[Third party Other costs],

[Total Third Party costs],

[Salary cost - Selling and Marketing],

[Salary cost - Network Maintenance],

[Salary cost - General and Admin],

[General and Admin expenses - Overheads],

[Total Internal costs],

[Total costs],

[Operating Margin before Depreciation],

Depreciation,

[Operating Margin after Depreciation],

[Legacy adjustment for IRU amortisation],

[Special Adjustment],

[Operating Profit for RPSM],

[Computation of Arms Length Profit under the Residual Profit Split Method],

[Average Operating Assets employed during the quarter],

[Return % on Operating Assets employed],

[Return on Average Operating Assets employed during the quarter],

Depreciation1,

[Adjustment for contract value],

[Depreciation eligible for reimbursement],

[Routine return on operating assets],

[Return % for Selling and Marketing],

[Return % for Network Maintenance],

[Return % for General and Admin],

[Return on Selling and Marketing Routine Function],

[Return on Network Maintenance Routine Function],

[Return on General Admin Routine Function],

[Total Return on Routine Functions],

[Total Routine Return on Operating Assets and Routine Functions],

[Residual Operating Profit / (Loss) for the group for the quarter],

[Value Added Driver Weighted Average ratio],

[Distribution of Residual Operating Profit / (Loss) to Tier 1 entities],

[Total Eligible Return],

[Target Revenue],

[Transfer Pricing Adjustment Receive / (Pay)],

[TPA Remuneration %],

Remuneration,

[Final Transfer Pricing Adjustment Income / (Expense)],

[Average Exchange rate],

[Closing Exchange rate]

FROM

(biff, embedded labels, header is 8 lines, table is [RPSM 04 to 04 2012$], filters(

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 2)),

Transpose()

));

tab2:

Concatenate

LOAD Particulars as Code2,

F2 as [Name of Entity] ,

[Gross Service Revenue billed to third party customers],

[Third Party Network costs],

[Third party Other costs],

[Total Third Party costs],

[Salary cost - Selling and Marketing],

[Salary cost - Network Maintenance],

[Salary cost - General and Admin],

[General and Admin expenses - Overheads],

[Total Internal costs],

[Total costs],

[Operating Margin before Depreciation],

Depreciation,

[Operating Margin after Depreciation],

[Legacy adjustment for IRU amortisation],

[Special Adjustment],

[Operating Profit for RPSM],

[Computation of Arms Length Profit under the Residual Profit Split Method],

[Average Operating Assets employed during the quarter],

[Return % on Operating Assets employed],

[Return on Average Operating Assets employed during the quarter],

Depreciation1,

[Adjustment for contract value],

[Depreciation eligible for reimbursement],

[Routine return on operating assets],

[Return % for Selling and Marketing],

[Return % for Network Maintenance],

[Return % for General and Admin],

[Return on Selling and Marketing Routine Function],

[Return on Network Maintenance Routine Function],

[Return on General Admin Routine Function],

[Total Return on Routine Functions],

[Total Routine Return on Operating Assets and Routine Functions],

[Residual Operating Profit / (Loss) for the group for the quarter],

[Value Added Driver Weighted Average ratio],

[Distribution of Residual Operating Profit / (Loss) to Tier 1 entities],

[Total Eligible Return],

[Target Revenue],

[Transfer Pricing Adjustment Receive / (Pay)],

[TPA Remuneration %],

Remuneration,

[Final Transfer Pricing Adjustment Income / (Expense)],

[Average Exchange rate],

[Closing Exchange rate]

FROM

(biff, embedded labels, header is 8 lines, table is [RPSM 04 to 05 2012$], filters(

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 3)),

Transpose()

));

NoConcatenate

tab3:

LOAD

tab2.code2 - tab1.code1 as diff

resident tab1;

--------------------------------------------------------------------------------

but it is giving me error:

Field not found - <tab2.code2>

NoConcatenate

tab3:

LOAD

tab2.code2 - tab1.code1 as diff

resident tab1

Re: row minus row?

CELAMBARASAN — Mon, 03 Mar 2014 09:42:21 GMT

Check the below steps

Qualify *;

UnQualify Code;

Table1:

Load

Excel file source 1;

Table2:

Load

Excel file source 2;

Create a straight table in chart and add Text fields in dimension and Expressions as

Table1.FieldName - Table2.FieldName

Hope it helps

Re: row minus row?

CELAMBARASAN — Mon, 03 Mar 2014 09:44:02 GMT

After concatenate it forms two table in to one. Table 2 no longer exists there to refer.

Re: row minus row?

Mon, 03 Mar 2014 09:56:26 GMT

But I want to do calculation in script and want to store result in a qvd.....

Re: row minus row?

Anonymous — Mon, 03 Mar 2014 10:11:19 GMT

Use join instead of concatenate

Re: row minus row?

CELAMBARASAN — Mon, 03 Mar 2014 10:16:06 GMT

Try this

Qualify *;

UnQualify Code;

Table1:

Load

Excel file source 1;

Left Join

Table2:

Load

Excel file source 2;

Result:

Load

Table1.FieldName - Table2.FieldName

Resident Table1;

Store Result Into result.qvd(qvd);

Re: row minus row?

Mon, 03 Mar 2014 10:53:52 GMT

This code is also giving same error.

Table not found

Result:

Load

Tab1.Code - Tab2.Code

Resident Tab1

Re: row minus row?

Anonymous — Tue, 04 Mar 2014 03:37:15 GMT

Can you post your script, we can have a look and may be help you out in much efficient way.

Re: row minus row?

CELAMBARASAN — Wed, 05 Mar 2014 06:53:23 GMT

Did you used below statements in your script?

Qualify *;

UnQualify Code;

Re: row minus row?

Thu, 06 Mar 2014 10:49:39 GMT

Yes... It worked. Thank you so much