topic Re: Sum Same value in same pivot in QlikView

Sum Same value in same pivot

pgalvezt — Mon, 09 Feb 2015 13:02:11 GMT

Hello,

I am creating several subgroups of sellers .. with if statements ... but when I put the same seller in another subgroup only considers me one ... apparently QlikView takes the seller repeated and shows only 1 time ... There is a way tells QlikView that adds the same seller 2 times in the same pivot table?

Salesman 1 (Only 1 sales man)

Salesman 2 (4 Salesman ... Salesman1 plus another 3)

Finally

Qlikview shows me this:

Salesman1 = 58.054.886 (OK)

Salesman2 = 42.429.607 (not Ok should be 100.484.493) Here salesman1 dissapear.

Total sum 158.539.379 (What I want)

Total sum 100.484.493 (I get this in qlikview)

I attached a qvw file and excel with what I want.

Hope you undertand the problem...

Thanks!!

Re: Sum Same value in same pivot

Anonymous — Mon, 09 Feb 2015 13:11:03 GMT

can you attach the text file(source)

Re: Sum Same value in same pivot

Anonymous — Mon, 09 Feb 2015 13:21:17 GMT

Looking into the script - Salesman 1 is not included in Salesman 2 group(?)

Re: Sum Same value in same pivot

pgalvezt — Mon, 09 Feb 2015 13:31:57 GMT

Hi michael,

I comment that line to show what I want. Sorry about that.

I remove the comment now you will see the problem.

Re: Sum Same value in same pivot

Anonymous — Mon, 09 Feb 2015 13:41:37 GMT

I see, the Salesman 1 can't be created this way. Try instead to concatenate, e.g.:

Table:

load

'Salesman2' as Ingresos,

...

WHERE match(vendero_desc,'Jorge Carrizo','Mario Roncoso','Nestor Morales','Paolo Rivera');

CONCATENATE (Table) LOAD

Salesman1' as Ingresos,

...

WHERE match('Nestor Morales');

Re: Sum Same value in same pivot

pgalvezt — Mon, 09 Feb 2015 14:31:26 GMT

Hi Michael,

I concatenated the 2 tables. But only takes Salesman1. so the sum is the same. Maybe there is something I did wrong.

I attached the results.

Re: Sum Same value in same pivot

Anonymous — Mon, 09 Feb 2015 14:50:26 GMT

I can see that you're loading only one vendor, Nestor Morales. As Jebamalai says, upload the source file so it would be possible to troubleshoot the script.

Re: Sum Same value in same pivot

robert_mika — Mon, 09 Feb 2015 14:54:57 GMT

This is what Michael is talking about

Re: Sum Same value in same pivot

pgalvezt — Mon, 09 Feb 2015 16:12:35 GMT

Ok,

I prepared a qlikview source with all 2014. And an excel file with the subgroups.

Re: Sum Same value in same pivot

Anonymous — Mon, 09 Feb 2015 16:58:00 GMT

See if the attached helps.

I changed match() to wildmatch(() because there is a mix of capitalization, and wildmatch() is not case sensitive. Also fixed one wrong name.

Re: Sum Same value in same pivot

pgalvezt — Mon, 09 Feb 2015 17:23:16 GMT

So,

If I want to add another line. I would have to concatenate another load with differents names, right?

Thank you for your help Michael...

Re: Sum Same value in same pivot

Anonymous — Mon, 09 Feb 2015 17:35:49 GMT

In general - yes, but there is no need to reload all every time. See another example, a little cleaner...