topic Re: Adding row in table in QlikView

Adding row in table

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

Hi,

I've got a table with two dimensions and several formulars.

I have to Display one additional row, which displays the sum of two other rows.

See attached file. The red row I have to insert, which contains the data of the two above.

Has anybody an idea?

Thanhx!

Re: Adding row in table

Venkatchoudary — Thu, 17 Aug 2017 16:30:47 GMT

you want to have sub total for each Country ?

Re: Adding row in table

veidlburkhard — Thu, 17 Aug 2017 19:10:02 GMT

Hi Martina,

without any knowledge of the data behind I can only guess.

If you try to reasonbly transfer this into your script you'll get an additional owner Gnewas & Almara:

T1:
Load
Country,
Owner,
Product,
Amount

From your source.qvd;

T2:

Load
Country,
‘Gnewas & Almara’ as Owner,
Product,
Amount

Resident T1
where Owner = ‘Almara’ or Owner = ‘Gnewas’;

The second Table automatically concatenates to T1 and you’ll have an additional owner Gnewas & Almara with all the products and amounts of Gnewas and Almara, placed in Indonesia.

Hope this helps

Burkhard

Re: Adding row in table

its_anandrjs — Fri, 18 Aug 2017 06:41:38 GMT

Hi,

You can go for some thing Profit and Loss statement dashboard in qlikview or create the Interval match table if required to you.

How to Create a Profit and Loss Statement in QlikView

Regards,

Anand

Re: Adding row in table

Anonymous — Fri, 18 Aug 2017 07:16:11 GMT

Not a subtotal for each Country, only the marked line is required.

Re: Adding row in table

Anonymous — Fri, 18 Aug 2017 07:18:28 GMT

Thanks for your replay, but I can't change the script in the recommended way, because it is very complex, several pages, a lot of data.... and the results in a lot of tables.....

The table I posted is only one and this one is reduced to the relevant Information.

Re: Adding row in table

Anonymous — Fri, 18 Aug 2017 07:23:16 GMT

Thanx, I can't see a relation between P&L and my data.

But maybe the interval match table could help. What do you mean with this table?

Re: Adding row in table

its_anandrjs — Fri, 18 Aug 2017 07:44:26 GMT

They are used to calculate the matched record start and end numbers only to get the total sum values.

Regards

Anand

Re: Adding row in table

veidlburkhard — Fri, 18 Aug 2017 11:00:04 GMT

Ok, Martina.

Then we try it in a different way.

1. Step

add this data island to your script (no changes to existing script):

CalcDim:

LOAD * INLINE [

Dimension

];

and perform a reload.

2. Step

(I assumed your table is a pivot table)

Create this calculated dimension in the dimension tab of your table:

=If(Country = 'Indonesia', Pick(Dimension, Owner, 'Gnewas & Almara'), Owner)

delete your old dimension 'Owner' and rename the new dimensin 'Owner' again.

Go to the Sort tab and check for 'Owner' sort by expression. Enter this expression

If(Country = 'Indonesia', Match(Pick(Dimension, Owner, 'Gnewas & Almara'), 'Gnewas', 'Almara', 'Gnewas & Almara', 'Berewic'), Owner)

Chose 'Ascending'.

Go to the expression tab and enter this expression for No of Products (I used the basic expression 'Sum(Products)' you should take your expression instead):

If(Country = 'Indonesia',

If(Dimensionality() = 1, Sum({$<Owner = {'Gnewas', 'Almara', 'Berewic'}>} Products), //this gives the subtotal of Indonesia

Pick(Dimension, Sum(Products), Sum({$<Owner = {'Gnewas', 'Almara'}>} Products))), //this gives the value of Gnewas & Almara

Sum(Products)).

At last you'll get this result:

You'll find the extra row and the subtotal of Indonesia is unaltered.

Hope this is ok for you

Burkhard

Re: Adding row in table

tresB — Fri, 18 Aug 2017 11:09:11 GMT

One sample here you can check:

Manually insert row within table

Calculations on rows in Load script