filter data in where statement

[markgraham123]

hi all,

I'm trying to load only those which have A in their location.

I/P:

Number, Location

1,909

1,A902

2,10812

2,A15433

3,A7422

4,A901235

4,75662

5,A730654

If it has A** and also other numbered location, i wanna exclude that.

Req: O/P

Numbers 3 and 5

Any help is highly appreciated

[MindaugasBacius]

Test:
LOAD * Inline
[
Number, Location
1,909
1,A902
2,10812
2,A15433
3,A7422
4,A901235
4,75662
5,A730654
] 
Where Left(Location, 1) = 'A'
;

Results:

[markgraham123]

Mindaugas,

I want to fetch data which has exclusively 'A' in the left.

In this case, i should get only, A7422 and A730654

[sunny_talwar · Accepted solution]

May be this?

Test:

LOAD * Inline [

Number, Location

1,909

1,A902

2,10812

2,A15433

3,A7422

4,A901235

4,75662

5,A730654

];

Right Join (Test)

LOAD Number

Where Count1 = Count2;

LOAD Number,

  Count(Location) as Count1,

  Count(If(Left(Location, 1) = 'A', Location)) as Count2

Resident Test

Group By Number;

[markgraham123]

You are awesome man!

No words left

It worked. Thanks!!!

[#6]

Sunny's answer is perfect. Here is another possibility:

Test:
LOAD * Inline
[
Number, Location
1,909
1,A902
2,10812
2,A15433
3,A7422
4,A901235
4,75662
5,A730654
];

Test2:
NoConcatenate
load *
Where Location Like 'A*';
load
Number,
Only(Location) as Location
Resident Test
group by Number;

DROP Table Test;