topic filter data in where statement in QlikView

filter data in where statement

markgraham123 — Tue, 13 Sep 2016 16:52:30 GMT

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

Re: filter data in where statement

MindaugasBacius — Tue, 13 Sep 2016 16:59:22 GMT

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:

Re: filter data in where statement

markgraham123 — Tue, 13 Sep 2016 17:03:12 GMT

Mindaugas,

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

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

Re: filter data in where statement

sunny_talwar — Tue, 13 Sep 2016 17:11:50 GMT

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;

Re: filter data in where statement

markgraham123 — Tue, 13 Sep 2016 17:14:07 GMT

You are awesome man!

No words left

It worked. Thanks!!!

Re: filter data in where statement

Tue, 13 Sep 2016 17:19:43 GMT

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;