Counter

[#1]

Hi,

I have the below table

KEY flag  Day

1A      1     0

1A     0     1    

1A     1     2

2A     0     0    

2A     1     1

3A     0     0    

3A     0     1

i need to generate another table with the KEY and a counter which indicates the number of 1's between 0 to n Day

KEY Counter

1A     2

2A     1    

3A     0

Please help me with the logic

[marcus_sommer · Accepted solution]

Try this:

Counter:

Load KEY, sum(flag) as Counter Resident table Group by KEY;

- Marcus

[buzzy996]

tab3:

load * inline [

KEY, flag,  Day

1A ,     1 ,    0

1A ,    0  ,   1 

1A  ,   1  ,   2

2A ,    0  ,   0 

2A ,    1  ,   1

3A  ,   0   ,  0 

3A  ,   0  ,   1

];

tab5:

load

KEY,

count(flag) as count

resident tab3

group by KEY;

[#4]

Thanks Marcus,

Suppose if there are more number of days and how can we find if Flag is 1 in once in 3 days?

Hoping for a solution

[marcus_sommer]

I don't know how the flag is created. Maybe you could change these logic or you could load these table additionally with a where-clause:

Load Key, flag as 1_flag Resident result where flag = 1;

It will be depend in which situations you want what to do.

- Marcus