topic Re: ID's depending on condition in QlikView

ID's depending on condition

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Kindly suggest solution for my below mentioned issue.

ID , Status

1 , Active

1 , In- Active

1 , Active

2 , In- Active

3 , Active

4 , Active

5 , Active

5 , In- Active

5 , Active

5 , In- Active

6 , Active

6 , In- Active

7 , Active

7 , In- Active

8 , In- Active

9 , Active

10 , In- Active

10 , Active

I want only those ID's who does not have In- Active Status .

My result will be :

Hope u guys got my requirement and will help me asap.

Regards,

Minal Gedam

Re: ID's depending on condition

avinashelite — Thu, 30 Jun 2016 10:05:42 GMT

Try like this

Temp:

LOAD

ID ,

Status

from table;

Result:

LOAD

ID,

concat(Status,',') as New_Status

wildmatch(concat(Status,','),'*In- Active*') as Flag

Resident

Temp

group by

ID;

in front end try check for the flag 0

Re: ID's depending on condition

avinashelite — Thu, 30 Jun 2016 10:07:52 GMT

if you need in the back end try like this

Temp:

LOAD

ID ,

Status

from table;

Result:

LOAD

ID,

concat(Status,',') as New_Status

wildmatch(concat(Status,','),'*In- Active*') as Flag

Resident

Temp

group by

ID;

Noconcatenate

LOAD

ID,

concat(Status,',') as New_Status

resident

Result

where Flag=0;

drop table Result;

Re: ID's depending on condition

PrashantSangle — Thu, 30 Jun 2016 10:09:46 GMT

Hi,

try like

Load Id ,Status

where not wildmatch(staus,'In-active')

Regards

Re: ID's depending on condition

Thu, 30 Jun 2016 10:15:58 GMT

Hi max,

Ur solution will not give the result as I mentioned in my post .

Pls suggest something else

Re: ID's depending on condition

Thu, 30 Jun 2016 10:29:57 GMT

Hi Avinash R,

I tried you solution but it is not working

Re: ID's depending on condition

PrashantSangle — Thu, 30 Jun 2016 10:44:13 GMT

Hi,

Complete code

Test:

LOAD * INLINE [

ID , Status

1 , Active

1 , In- Active

1 , Active

2 , In- Active

3 , Active

4 , Active

5 , Active

5 , In- Active

5 , Active

5 , In- Active

6 , Active

6 , In- Active

7 , Active

7 , In- Active

8 , In- Active

9 , Active

10 , In- Active

10 , Active

];

Temp:

Load ID as InActiveID,

Status as NEwStat

Resident Test

where WildMatch(Status,'In- Active');

NoConcatenate

Final:

Load * Resident Test

where not Exists(InActiveID,ID);

Drop table Temp,Test;

Regards,

Prashant

Re: ID's depending on condition

PrashantSangle — Thu, 30 Jun 2016 11:57:03 GMT

Hi,

Above code giving me below output.

Your required output is

which you mention in your post. I don't get it why it is not working at your end.

Can you post your sample app.

Regards,

Prashant

Re: ID's depending on condition

PrashantSangle — Thu, 30 Jun 2016 12:12:07 GMT

Hi,

Please find attachment for your reference.

Regards,

Prashant

Re: ID's depending on condition

veeranj — Thu, 30 Jun 2016 12:13:17 GMT

Hi,

please check the below code .

temp:

LOAD * INLINE [

id, status

1, active

1, inactive

1, active

2, active

2, inactive

3, active

3, inactive

4, active

5, active

];

ids_having_atleast_one_inactivestatus:

load id as id1 Resident temp where status='inactive';

output:

noconcatenate

load *,'required output' as output Resident temp where not exists(id1,id) ;

drop tables temp,ids_having_atleast_one_inactivestatus;

here output will be 4 and 5 as per my input .

Thanks,

Anjee

Re: ID's depending on condition

sunny_talwar — Fri, 01 Jul 2016 01:04:27 GMT

If you want to get rid of the ID's which have In- Active through the script itself, you can try this:

Table:

LOAD * Inline [

ID, Status

1, Active

1, In- Active

1, Active

2, In- Active

3, Active

4, Active

5, Active

5, In- Active

5, Active

5, In- Active

6, Active

6, In- Active

7, Active

7, In- Active

8, In- Active

9, Active

10, In- Active

10, Active

];

Right Join (Table)

LOAD ID

Where Flag = 1;

LOAD Distinct ID,

If(Count(If(Status = 'In- Active', Status)) > 0, 0, 1) as Flag

Resident Table

Group By ID;

Output:

Note that you have excluded the other ID's in the script itself and will not be able to use them in your application. If you still want to use the other ids, create a flag so that you know when ID's don't have In- Active status and can be easily used in set analysis.

Table:

LOAD * Inline [

ID, Status

1, Active

1, In- Active

1, Active

2, In- Active

3, Active

4, Active

5, Active

5, In- Active

5, Active

5, In- Active

6, Active

6, In- Active

7, Active

7, In- Active

8, In- Active

9, Active

10, In- Active

10, Active

];

Left Join (Table)

LOAD Distinct ID,

If(Count(If(Status = 'In- Active', Status)) > 0, 0, 1) as Flag

Resident Table

Group By ID;

Output:

I guess there must be numerous other ways you can do this, but you have to check which is the best approach that fits your need.

HTH

Best,

Sunny

P.S.

Method1 - Community_223183.qvw

Method2 - Community_223183_v1.qvw