Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
maxgro
MVP
MVP

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;

View solution in original post

7 Replies
miskinmaz
Creator III
Creator III

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
Author

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

sunny_talwar

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

miskinmaz
Creator III
Creator III

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;

maxgro
MVP
MVP

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
Author

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

Not applicable
Author

Thank you! Working well.