Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

crazyhorse66
New Contributor II

Select if exists in column

Hi - thanks in advance for any replies.

I have a table which has the following fields:

File No

Owner

Worked on By

I need to create a report with a dimension of Worked on By and count(File No) - this works fine

However I also need the ffollowing which is proving more difficult:

If Worked on By is in (Select distinct Owner) - flag as '1', otherwise flag as '0'

I need to have the table with Worked on By and count(File No) with an additional column, and the value in this column depends if they are an Owner or not.

So something like if they exist in the Owner column flag as '1'

Hope this makes sense, thanks again

Tags (4)
1 Solution

Accepted Solutions
Not applicable

Re: Select if exists in column

Same Exists would do the job for you.

LOAD IF(Exists(owner,handler) or owner = handler, 'Owner ','Assistant' ) as handlertype,

      id as [TR ID],

    caseno as [TR Case No],

    daterecorded as [TR Date],

    actiontype as [TR Action Type],

    owner as [Owner],

    handler as [Worked on By];

SQL SELECT *

FROM table;

I have create dummy working application. Have a look at the attched application.

Hope this helps you.

- Sridhar

6 Replies
IAMDV
Honored Contributor II

Re: Select if exists in column

Hi,

Please can you post the sample QV document? It will be easy to try with some data...

Thanks - DV

crazyhorse66
New Contributor II

Re: Select if exists in column

Sorry I don't have a sample document but here's some of the code:

TimeRecording:

LOAD id as [TR ID],

    caseno as [TR Case No],

    daterecorded as [TR Date],

    actiontype as [TR Action Type],

    owner as [Owner],

    handler as [Worked on By],;

SQL SELECT *

FROM table

WHERE daterecorded >= '2010-11-01'

AND caseno in (SELECT caseno FROM table2);

Many Thanks

Not applicable

Re: Select if exists in column

Your code should be as follows.

Table2:

Load * ;

SELECT caseno FROM table2;

LOAD id as [TR ID],

    caseno as [TR Case No],

    daterecorded as [TR Date],

    actiontype as [TR Action Type],

    owner as [Owner],

    handler as [Worked on By] Where exists(caseno,caseno ) and daterecorded >= Date(Date#('2010-11-01','YYYY-MM-DD)','YYYY-MM-DD'); //Make sure that Daterecorded date formate matches with 'YYYY-MM-DD'

SQL SELECT * FROM table;

Drop Table Table2;

-Sridhar

crazyhorse66
New Contributor II

Re: Select if exists in column

Thanks for your reply, I may not have described the issue

This statement works fine.

TimeRecording:

LOAD id as [TR ID],

    caseno as [TR Case No],

    daterecorded as [TR Date],

    actiontype as [TR Action Type],

    owner as [Owner],

    handler as [Worked on By];

SQL SELECT *

FROM table;

However I also need something like

handlertype as Owner if the value in handler exists somewhere in owner field (not just the same row)

otherwise handlertype as Assistant

Hope that makes it clearer, thanks again

Not applicable

Re: Select if exists in column

Same Exists would do the job for you.

LOAD IF(Exists(owner,handler) or owner = handler, 'Owner ','Assistant' ) as handlertype,

      id as [TR ID],

    caseno as [TR Case No],

    daterecorded as [TR Date],

    actiontype as [TR Action Type],

    owner as [Owner],

    handler as [Worked on By];

SQL SELECT *

FROM table;

I have create dummy working application. Have a look at the attched application.

Hope this helps you.

- Sridhar

crazyhorse66
New Contributor II

Re: Select if exists in column

Excellent - thanks for your quick response

Community Browser