topic Re: Flag the values by ID in QlikView

Flag the values by ID

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

Please help us to flag the ID's by 'A'. If the 'A' come alone, flag as Single, If the 'A' comes with other values like 'B' 'C', flag as multiple.

Ex: ID=1 has multiple values in field

ID=2 has only 'A' in field

LOAD * INLINE [

ID, Field

1, A

1, B

1, C

2, A

3, A

3, B

4, A

5, A

5, B

5, C

6, A

];

Re: Flag the values by ID

sunny_talwar — Mon, 18 Jun 2018 14:21:30 GMT

Try this

Table:

LOAD * INLINE [

ID, Field

1, A

1, B

1, C

2, A

3, A

3, B

4, A

5, A

5, B

5, C

6, A

];

Left Join (Table)

LOAD ID,

If(Count(DISTINCT Field) = 1, 'Single', 'Multiple') as Flag

Resident Table

Group By ID;

Re: Flag the values by ID

karan_kn — Mon, 18 Jun 2018 16:53:49 GMT

Hi Sunny, Thanks for your reply, my requirement is the Field contains 'A' only it should consider, rest of the field values can ignore.

Table:

LOAD * INLINE [

ID, Field

1, A

1, B

1, C

2, A

3, A

3, B

4, A

5, A

5, B

5, C

6, A

7, B

8, C

];

Re: Flag the values by ID

sunny_talwar — Mon, 18 Jun 2018 16:58:04 GMT

May be this

Table:

LOAD * INLINE [

ID, Field

1, A

1, B

1, C

2, A

3, A

3, B

4, A

5, A

5, B

5, C

6, A

7, B

8, C

];

Left Join (Table)

LOAD ID,

1 as TempFlag

Resident Table

Where Field = 'A';

Left Join (Table)

LOAD ID,

If(Count(DISTINCT Field) = 1, 'Single', 'Multiple') as Flag

Resident Table

Where TempFlag = 1

Group By ID;

Re: Flag the values by ID

MarcoWedel — Mon, 18 Jun 2018 20:53:55 GMT

Hi,

another solution might be:

table1:
LOAD * INLINE [
    ID, Field
    1, A
    1, B
    1, C
    2, A
    2, A
    3, A
    3, A
    3, B
    4, A
    5, A
    5, B
    5, C
    6, A
    6, A
    7, B
    8, C
    8, C
];

Join
LOAD ID,
    If(Min(Field='A'),If(Count(DISTINCT Field)>1,'Multiple','Single')) as Flag   
Resident table1
Group By ID;

hope this helps

regards

Marco