Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Hi,
Hope the attached qvw helps.
What values your FinalTable Expecting?
Like this
See Attachment.
Regards,
Antonio
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;
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.
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.
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.
Hi Hemanth,
Hope you missed my attachment. Fine the attached qvw with the result you are expecting.