Count Rows with specific Field-Value

[robert_gansel]

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

[sathishkumar_go]

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

[Gysbert_Wassenaar]

Try these expressions:

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

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