topic Re: Help required in getting count in QlikView

Help required in getting count

manojkulkarni — Mon, 25 Jul 2016 10:22:29 GMT

Hi All,

I have given sample data, where in each IP Address will be provided with 2 sources. I need identify the no of address assigned by Source wise considering both primary & Secondary for each IP. Please suggest how this can be achieved ?

Data :

IPAddress	Primary	Secondary
1	L1	L3
2	L1	L3
3	L1	L2
4	L2	L1
5	L2	L3
6	L3	L1
7	L4

Expected Result

Sources	CountIP
L1	5
L2	3
L3	4
L4	1

Re: Help required in getting count

effinty2112 — Mon, 25 Jul 2016 10:38:01 GMT

Hi Manoj,

If a script solution is okay for you:

Data:

LOAD IPAddress,

Primary,

Secondary

FROM

[https://community.qlik.com/thread/226368]

(html, codepage is 1252, embedded labels, table is @1);

TempCrossData:

CrossTable(Classification, Source) LOAD * Resident Data;

NoConcatenate

Crossdata:

LOAD * Resident TempCrossData where Len(Source)>0;

DROP Table TempCrossData;

then we can get this straight table:

Source	Count(IPAddress)
	13
L1	5
L2	3
L3	4
L4	1

Re: Help required in getting count

PrashantSangle — Mon, 25 Jul 2016 10:43:19 GMT

Hi,

one possible solution can be

Load IPAddress as IP

Primary as Sources

From table;

concatenate

Load IPAddress as IP

Secondary as Sources

From table;

then on front end take straight table

Sources as dimension

and expression count(IP)

Regards

Re: Help required in getting count

its_anandrjs — Mon, 25 Jul 2016 10:47:30 GMT

You can try this way also

Data:
LOAD * Inline
[
IPAddress, Primary,Secondary
1, L1, L3
2, L1, L3
3, L1, L2
4, L2, L1
5, L2, L3
6, L3, L1
7, L4,
];

Test:
LOAD
IPAddress,Primary As Level
Resident Data;

Concatenate(Test)
LOAD
IPAddress, Secondary as Level
Resident Data;

Query:
LOAD
Level as Source,Count(IPAddress) as CIP
Resident Test
Group By Level

Regards,

Anand

Re: Help required in getting count

effinty2112 — Mon, 25 Jul 2016 10:47:37 GMT

Hi Manoj,

Here's a front end solution:

=ValueList('L1','L2','L3','L4')	sum(if(Primary=ValueList('L1','L2','L3','L4'),1))+sum(if(Secondary=ValueList('L1','L2','L3','L4'),1))
	13
L1	5
L2	3
L3	4
L4	1

Re: Help required in getting count

manojkulkarni — Mon, 25 Jul 2016 10:53:56 GMT

Thanks for your suggestion. But I am looking for front end solution, since this needs to be dynamic

Re: Help required in getting count

manojkulkarni — Mon, 25 Jul 2016 10:55:18 GMT

Hi Andrew,

Thanks for your suggestion. Is there way to make it dynamic rather defining it in value list

Re: Help required in getting count

its_anandrjs — Mon, 25 Jul 2016 10:58:09 GMT

If you need dynamic so create this count in script level as i suggest you in the above post see my post.

Regards,

Anand

Re: Help required in getting count

effinty2112 — Mon, 25 Jul 2016 11:40:53 GMT

Hi Manoj,

Use the calculated dimension:

=ValueList($(=chr(39) & Concat(DISTINCT Primary,Chr(39)&','&Chr(39)) & chr(39)))

and the expression:

sum(if(Primary=ValueList($(=chr(39) & Concat(DISTINCT Primary,Chr(39)&','&Chr(39)) & chr(39))),1))+sum(if(Secondary=ValueList($(=chr(39) & Concat(DISTINCT Primary,Chr(39)&','&Chr(39)) & chr(39))),1))

gives this:

Sources	Count IP
	13
L1	5
L2	3
L3	4
L4	1

The only problem is that the value list is made up by going through all the primary sources if there will be sources that are secondary and and don't exist as primary then this will fail.

If there are sources which are secondary but never primary then this appears to work but i would subject to testing:

Dimension:=ValueList($(=chr(39) & Concat(DISTINCT Primary,Chr(39)&','&Chr(39)) & chr(39)),$(=chr(39) & Concat(DISTINCT Secondary,Chr(39)&','&Chr(39)) & chr(39)))

Expresion: sum(if(Primary=ValueList($(=chr(39) & Concat(DISTINCT Primary,Chr(39)&','&Chr(39)) & chr(39)),$(=chr(39) & Concat(DISTINCT Secondary,Chr(39)&','&Chr(39)) & chr(39))),1))+sum(if(Secondary=ValueList($(=chr(39) & Concat(DISTINCT Primary,Chr(39)&','&Chr(39)) & chr(39)),$(=chr(39) & Concat(DISTINCT Secondary,Chr(39)&','&Chr(39)) & chr(39))),1))

this gives us

Sources	Count IP
	14
L1	5
L2	3
L3	4
L4	1
	1

It's including a null / empty source corresponding to the secondary source of IP address 7.

Cheers

Andrew

Re: Help required in getting count

manojkulkarni — Mon, 25 Jul 2016 11:50:59 GMT

Thanks Andrew for quick reply & solution. It worked.

One more thing, if i need to use the value of dimension into a expression how should we achieve in this ? I though of using P() function, will it work ?

Re: Help required in getting count

effinty2112 — Mon, 25 Jul 2016 12:03:44 GMT

Hi Manoj,

If you want to use the calculated dimension in an expression just copy the Valuelist formula into your expression as if was a dimension field.

Here the whole ValueList formula in inserted into a Right() expression to get the number part.

Error: Garbage after expression: "L1'"	Right(ValueList('L1','L2','L3','L4'),1)

L1	1
L2	2
L3	3
L4	4

cheers

Andrew

Re: Help required in getting count

manojkulkarni — Mon, 25 Jul 2016 12:26:45 GMT

May be question is not clear. What I meant is If want to use the Dimension Value "L1" or "L2" in set analysis of expression then how should we use ?