Help required in getting count

[manojkulkarni]

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

[effinty2112]

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

[PrashantSangle]

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

[its_anandrjs]

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

[effinty2112]

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

[manojkulkarni]

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

[manojkulkarni]

Hi Andrew,

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

[its_anandrjs]

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

Regards,

Anand

[effinty2112 · Accepted solution]

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

[manojkulkarni]

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 ?