topic Re: Split data for specific cells only in QlikView

Split data for specific cells only

Wed, 28 Nov 2012 10:23:07 GMT

Hello,

I have the following data:

Title	DwnID	Type	Downloads
Title1	1	same	20
Title2	1	same	0
Title3	2	same	40
Title4	2	same	0
Title5	3	dif	60
Title6	4	dif	80

and I want to split the Downloads for titles which have the same DwnID:

Title	DwnID	Type	Downloads
Title1	1	same	10
Title2	1	same	10
Title3	2	same	20
Title4	2	same	20
Title5	3	dif	60
Title6	4	dif	80

If I use Sum({<Type={'same'}>} Downloads/2) I get the right values for Titles which already have a Downloads value but for the rest the value stays '0'.

Any suggestions?

Kind Regards,

Ivelin

Re: Split data for specific cells only

Gysbert_Wassenaar — Wed, 28 Nov 2012 10:51:23 GMT

You can calculate it with avg(total <DwID> Downloads). See attached example.

Re: Split data for specific cells only

Wed, 28 Nov 2012 11:29:23 GMT

Hey thanks Gysbert but it doesn't really work in my case.

Downloads/2 was also only one of the cases I have, for others the calculation should be Downloads*30%, for example.

Re: Split data for specific cells only

sujeetsingh — Wed, 28 Nov 2012 11:51:56 GMT

Why dont you give sample

Re: Split data for specific cells only

Wed, 28 Nov 2012 12:41:08 GMT

Title1 and Title2 have 20 and 0 in downloads according to the first table and in reality they both have 10 downloads each. That's because these 2 titles share the same DwnID. On the other side, Titles 5 and Title6 have different DwnIDs and thus the values of 60 and 80 are the correct.

Title	DwnID	Type	Downloads
Title1	1	same	=20*50%
Title2	1	same	=20*50%
Title3	2	same	=40*50%
Title4	2	same	=40*50%
Title5	3	dif	60
Title6	4	dif	80

Re: Split data for specific cells only

Wed, 28 Nov 2012 13:20:28 GMT

Here it is

Re: Split data for specific cells only

Gysbert_Wassenaar — Wed, 28 Nov 2012 14:09:52 GMT

See attached qvw

Re: Split data for specific cells only

Wed, 28 Nov 2012 15:01:59 GMT

That's what I'm looking for, thanks!

Re: Split data for specific cells only

Mon, 03 Dec 2012 10:32:02 GMT

Hi Gysbert,

I encountered an issue with this solution - selecting a Title returns its original value instead. Is there another way around it and/or is it possible/better to do it in the Script?

Re: Split data for specific cells only

Gysbert_Wassenaar — Mon, 03 Dec 2012 11:12:12 GMT

You could try this:

sum({1<DwnID=p(DwnID)>} total <DwnID> Downloads )/count({1<DwnID=p(DwnID)>} total <DwnID> DISTINCT Title)

If you select a title you get the list of titles with the same DwID as the selected title.

But it's probably better done in the script. Maybe adding this will do the trick:

join load sum(Downloads)/count(distinct Title) as SplitDwnl, DwnID

resident Table1 group by DwnID;

Re: Split data for specific cells only

Mon, 03 Dec 2012 15:32:33 GMT

I couldn't make the Join Load work with the applymap on and it looks like that's the very cause of the problem. If I remove it then each pair of Same Titles get assigned the full value and I can simply divide those by 2: If(Type='same', Sum(Downloads)/2, Sum(Downloads)). Removing applymap however, creates synthetic tables when I connect other data from other sources.