Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
robert_gansel
New Contributor III

Count Rows with specific Field-Value

Hi Community,

does anyone have an idea how I solve this in the load script:

NoOfRows WHERE value of column A(only in current line) is value of column B (for the whole table)

Example-Table to load:

A          B          C                   

01          01     01                   

01001     01     01001          

02          02     02                   

03          03     03                   

03001     03     03001          

03002     03     03002           

Result:

A          B          C                    NoOfRows(B)               NoOfRows(C)

01          01     01                    2                                   1

01001     01     01001             0                                   1

02          02     02                    1                                   1

03          03     03                    3                                   1

03001     03     03001             0                                  1

03002     03     03002             0                                  1

Many thanks for any help

Robert

2 Replies
Highlighted
Partner
Partner

Re: Count Rows with specific Field-Value

Hi,

Try this

Test:
LOAD * INLINE [
A , B , C 
01 , 01 , 01 
01001 , 01 , 01001 
02 , 02 , 02 
03 , 03 , 03 
03001 , 03 , 03001 
03002 , 03 , 03002 

]
;

Left JOIN (Test)
load
B as A,
COUNT(B) AS NoOfRows_B
Resident Test Group by B;

Left JOIN (Test)
load
C as A,
COUNT(C) AS NoOfRows_C
Resident Test Group by C;

-Sathish

MVP & Luminary
MVP & Luminary

Re: Count Rows with specific Field-Value

Try these expressions:

SubStringCount($(=chr(39) & concat('/' & B & '/' ,'|') & chr(39)) ,'/' & A & '/')

SubStringCount($(=chr(39) & concat('/' & C & '/' ,'|') & chr(39)) ,'/' & A & '/')


talk is cheap, supply exceeds demand