topic Re: Add two rows in pivot table & straight table in QlikView

Add two rows in pivot table & straight table

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

I have to add two different rows of the same column in a pivot table & straight table and put the data in the same row. for eg. below there are two rows china>>india and india>>china and 3rd row represent the sum of the above two row(in red).

corridor	amount

china>>india	100
india>>china	200
china>>india	300

Re: Add two rows in pivot table & straight table

shiveshsingh — Thu, 22 Mar 2018 06:39:37 GMT

Put total in last row and provide label name

Re: Add two rows in pivot table & straight table

Anonymous — Fri, 23 Mar 2018 12:42:06 GMT

Hi shivesh,

I don't want total I want to add two and put them into another row

Re: Add two rows in pivot table & straight table

Anil_Babu_Samineni — Sat, 24 Mar 2018 01:17:06 GMT

May be provide 10 Rows and expected result set as this won't met simple solution

Re: Add two rows in pivot table & straight table

Anil_Babu_Samineni — Sun, 25 Mar 2018 10:54:54 GMT

PFA

Re: Add two rows in pivot table & straight table

Anonymous — Mon, 26 Mar 2018 06:40:48 GMT

Hi anil,

I have data of trade corridor from one country to another and vice a versa. for eg. China>>UK, UK>>china and so on. Now I have to make a pivot table in which I need to show the data for top 10 corridors which I decide from my side which corridor has to be shown there. Now the one corridor in that can be India>>US but I want this should contains value of India>>US + US >> India both. I mean sum of the forward and backward flow. How can I do that ? for eg

india >> US = 200

US >> india = 300

china >> hong kong = 100

hong kong >> china 50

UK >> US =60

US >> UK = 90

then in the pivot table I should get

india >> US = 500

china >> hong kong = 150

UK >> US =150

summation of the both side trade flow should be there in one row

thank you

Re: Add two rows in pivot table & straight table

ChennaiahNallani — Mon, 26 Mar 2018 07:24:05 GMT

Try like below:

Tab:

Load * Inline [

corridor,amount

china>>india,100

india>>china,200

china>>india,300

];

Tab_1:

Load

corridor,

sum(amount) as Sum_Amount

Resident Tab

Group by corridor;

Drop Table Tab;

dimension

corridor

Expression

Sum(Sum_Amount)

Re: Add two rows in pivot table & straight table

Anonymous — Mon, 26 Mar 2018 08:00:16 GMT

Hi chennaiah,

I don't need the sum of the same corridor rather I need the sum of the forward and backward flow corridor.

for eg.

india >> US = 200

US >> india = 300

china >> hong kong = 100

hong kong >> china 50

UK >> US =60

US >> UK = 90

then in the pivot table I should get

india >> US = 500 = india>>US + US>> india

china >> hong kong = 150 = china>> hong kong + Hong kong>> china

UK >> US =150 = UK >> US + US>>UK

Re: Add two rows in pivot table & straight table

Anil_Babu_Samineni — Mon, 26 Mar 2018 13:10:08 GMT

The left side part data comes from Field? I can think something like below

If(SubField(FieldName, '>>', -1) = SubField(FieldName, '>>', 1), Sum(Measure))

If(SubField(FieldName, '>>', -1) = SubField(FieldName, '>>', 1), 1,0) as Flag

and measure should be

Sum({<Flag = {1}>} Measure)

Re: Add two rows in pivot table & straight table

Anonymous — Tue, 27 Mar 2018 14:44:18 GMT

Hi anil,

thank you but it is not giving the required result.

Re: Add two rows in pivot table & straight table

olivierrobin — Tue, 27 Mar 2018 14:59:11 GMT

hello

try something like this

tab2:
load rangeminstring(subfield(corridor,'>>',1),subfield(corridor,'>>',2)) as from,

rangemaxstring(subfield(corridor,'>>',1),subfield(corridor,'>>',2)) as to,
value
resident Tab;

and sum your data by dimensions from and to

with maybe ltrim and rtrim to remove white spaces

Re: Add two rows in pivot table & straight table

Anonymous — Wed, 28 Mar 2018 13:45:18 GMT

Hi anil,

Can u please give me the expressions which you have written. I can see in the column missing expressions.

Re: Add two rows in pivot table & straight table

olivierrobin — Wed, 28 Mar 2018 14:11:24 GMT

the expressions are above

i created a new tab tab2 and my graph was made based on this table

from ans to as dimensions

sum(value) as measure