| A | B | C |
| 1 | a | yes |
| 1 | b | yes |
| 1 | c | yes |
| 2 | d | no |
| 2 | e | no |
| 3 | f | yes |
| 3 | g | no |
| 3 | h | yes |
| 3 | i | no |
| 4 | j | yes |
I have the above Data and need to get like below
| A | B | C | Is_slipped |
| 1 | a | yes | Y |
| 1 | b | yes | Y |
| 1 | c | yes | Y |
| 2 | d | no | n |
| 2 | e | no | n |
| 3 | f | yes | partial |
| 3 | g | no | partial |
| 3 | h | yes | partial |
| 3 | i | no | partial |
| 4 | j | yes | Y |
the requirement is for each A Is_slipped is 'Y' when C is yes for all B and Is_slipped is 'N' when C is no for all B and Is_slipped is 'Partial' when C is either yes or no for all B
Test:
LOAD A,
B,
C
FROM
(ooxml, no labels, table is Sheet1);
left join(Test)
LOAD A,
Sum(if(C='yes',1)) as Y,
sum(if(C='no',1)) as N
Resident Test
Group by A;
Left Join(Test)
LOAD A,
Sum(if(C=0,1)) as dummy,
if(Y=0,'No',if(N=0,'Yes','Partial')) as is_slipped
Resident Test
Group by A;
But throwing error as "Invalid Expression". Please suggest.
