topic Display only those id's showing both status. in QlikView

Display only those id's showing both status.

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Please help me solve the below scenario ASAP as I am stuck in it.

I want those ID who have both status(i.e. In-queue and un-processed) is falling against same ID.

For example, I have column Status(In-queue, un-processed,processed, completed) and ID. i want all ID whose status( In-queue and un-processed) .

ID Status

1 In-queue

1 un-processed

2 processed

2 completed

3 In-queue

4 completed

5 In-queue

5 un-processed

6 completed

7 In-queue

7 un-processed

so my result will be ID: 1,5,7

Pls reply urgently.

Thanks in Advance .

Re: Display only those id's showing both status.

PrashantSangle — Thu, 09 Jun 2016 13:30:12 GMT

Hi,

try in where clause

Load * from tableName

where status='In-queue' and status='un-processed'

Regards,

Re: Display only those id's showing both status.

sunny_talwar — Thu, 09 Jun 2016 13:54:35 GMT

Try this may be:

Table:

LOAD * INLINE [

ID, Status

1, In-queue

1, un-processed

2, processed

2, completed

3, In-queue

4, completed

5, In-queue

5, un-processed

6, completed

7, In-queue

7, un-processed

];

Right Join (Table)

LOAD ID

Where Flag = 2;

LOAD ID,

Count(DISTINCT Status) as Flag

Resident Table

Where Match(Status, 'In-queue', 'un-processed')

Group By ID;

Re: Display only those id's showing both status.

Fri, 10 Jun 2016 04:45:12 GMT

Hello Sunny T,

Can you explain me, why you have use Flag = 2;

Re: Display only those id's showing both status.

PrashantSangle — Fri, 10 Jun 2016 10:34:33 GMT

Hi,

another solution can be

Table:

LOAD * INLINE [

ID, Status

1, In-queue

1, un-processed

1, new

2, processed

2, completed

3, In-queue

4, completed

5, In-queue

5, un-processed

6, completed

7, In-queue

7, un-processed

];

Test:

Load ID as new, count(Status) as newCo

Resident Table

Group by ID;

Test1:

Load new as newID

Resident Test

where newCo=2;

Left join

Load ID as newID,

Status

Resident Table;

FinalTest:

Load newID as FinalID,Status as FinalStatus Resident Test1

where wildmatch(Status,'In-queue','un-processed');

Drop table Table,Test,Test1;

Regards,

Re: Display only those id's showing both status.

sunny_talwar — Fri, 10 Jun 2016 10:39:46 GMT

I did a distinct count of status, but I have used a where statement which only brings in the two type of statuses. So in essence I am trying to pull those ID's where the distinct count of statuses = 2 and where those two status matches In-queue and un-processed.

Does it make sense?

Re: Display only those id's showing both status.

Sat, 11 Jun 2016 08:08:20 GMT

Hi All,

Thanks Sunny and Max Dreamer for solution.

Warm Regards,

Ashvita.

Re: Display only those id's showing both status.

sunny_talwar — Sat, 11 Jun 2016 13:54:05 GMT

Not a problem. We are glad we were able to help.

Best,

Sunny