How to get the COUNT of inactive regions?

[hemanthaanichet]

Hi QV People,

I have a data like below

Table 1:

Region	Region code	Branch Code
1	101	11
2	102	12
3	103	13
4	104	14
5	105	15
6	106	16
7	107	17
8	108	18
9	109	19
10	110	20

Table 2:

Region Code	Customer no	Loan Name
101	A001	Bike
102	B001	Car
103	C001	Supermarket
101	A002	Housing
104	D001	Business
102	B002	Personal
103	C002	Medical
104	D002	Housing
105	E001	Car
110	J001	Bike

My query is

In the above data only few regions have an entries in providing the loan to customer and remaining region doesn't have entries

1.Now i want to get the count of regions which doesn't have entries

2. i want to display those region as inactive region code

Regards,

Hemanth

[sunny_talwar]

May be this:

Dimension

Region

Expression

Sum(If(Len(Trim([Loan Name])) = 0, 1, 0)

For your second query, are you looking to do this in the script?

[krishnacbe]

Hi,

Hope the attached qvw helps.

[Anil_Babu_Samineni]

What values your FinalTable Expecting?

[antoniotiman]

Like this

See Attachment.

Regards,

Antonio

[el_aprendiz111]

hi

Table1:

LOAD * Inline

[

Region,Region Code,Branch Code

1,101,11

2,102,12

3,103,13

4,104,14

5,105,15

6,106,16

7,107,17

8,108,18

9,109,19

10,110,20

];

Left Join(Table1)

Table2:

LOAD * Inline

[

Region Code,Customer no,Loan Name

101,A001,Bike

102,B001,Car

103,C001,Supermarket

101,A002,Housing

104,D001,Business

102,B002,Personal

103,C002,Medical

104,D002,Housing

105,E001,Car

110,J001,Bike

];

SUMARY:

LOAD *, IF(IsNull([Customer no]),1,0) AS inactive_region Resident Table1;

DROP Table Table1;

[hemanthaanichet]

Hi Sunny,

Thanks for the help, but it throws some error. Here i need only count of the inactive region.

First Query i solve it like :

count( distinct [Table 1.Region Code]) - count(distinct [Table 2.Region Code])

I need to show the list of Inactive Regions in a LIST BOX.

Inactive Region

106

107

108

109

Thanks in advance.

[hemanthaanichet]

final output for the second query

I need to show the list of Inactive Regions in a LIST BOX.

Inactive Region

106

107

108

109

Thanks in advance.

[hemanthaanichet]

Hi,

Thanks for the help

First Query i solve it like :

count( distinct [Table 1.Region Code]) - count(distinct [Table 2.Region Code])

I need to show the list of Inactive Regions in a LIST BOX.

Inactive Region

106

107

108

109

Thanks in advance.

[krishnacbe]

Hi Hemanth,

Hope you missed my attachment. Fine the attached qvw with the result you are expecting.