Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Retrieving Data Through Identifiers

Hi!

I have a table with that looks like this:

ID    Item

1     ItemA

5     ItemF

4     ItemS

1     ItemJ

1     ItemB

4     ItemG

5     ItemI

...

1     ItemZ

10   ItemD

How can I output all IDs that have more than 1 item associated to them? For example:

1

4

5

Note: ID's with only one item are not included. Thank you!

1 Solution

Accepted Solutions
MVP
MVP

Re: Retrieving Data Through Identifiers

1.png



source:

load * inline [

ID,    Item

1  ,   ItemA

5   ,  ItemF

4    , ItemS

1     ,ItemJ

1,     ItemB

4 ,    ItemG

5  ,   ItemI

1   ,  ItemZ

10   ,ItemD

];

load * Where cnt > 1;

load

  ID,

  Concat(Item, ', '),

  count(Item) as cnt

Resident source

group by ID;

DROP Table source;

7 Replies
miskinmaz
New Contributor III

Re: Retrieving Data Through Identifiers

Hi,

At Script level, you can use expression of count(Id) like:

Load

  Id,

    if(count(Id)>1, Id) as newid

Resident Table1

Group by Id;

At front end, you can use same expression as measures in table.

Not applicable

Re: Retrieving Data Through Identifiers

Thank you for the response. Yes it will work. But I forgot to mention that I also need to output the Items associated with those id's. Any idea on that? Thank you

MVP
MVP

Re: Retrieving Data Through Identifiers

Are you looking to completely get rid of them in the script or are you just trying to flag them?

miskinmaz
New Contributor III

Re: Retrieving Data Through Identifiers

You need to left join the original table, this option is good as far you don't have any other fields

Like

left join

Load

  Id as newid,

  Name

Resident Table1;

MVP
MVP

Re: Retrieving Data Through Identifiers

1.png



source:

load * inline [

ID,    Item

1  ,   ItemA

5   ,  ItemF

4    , ItemS

1     ,ItemJ

1,     ItemB

4 ,    ItemG

5  ,   ItemI

1   ,  ItemZ

10   ,ItemD

];

load * Where cnt > 1;

load

  ID,

  Concat(Item, ', '),

  count(Item) as cnt

Resident source

group by ID;

DROP Table source;

Not applicable

Re: Retrieving Data Through Identifiers

Just want to output them or maybe record them in a different table

Not applicable

Re: Retrieving Data Through Identifiers

Thank you! Working well.