Retrieving Data Through Identifiers

[#1]

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!

[miskinmaz]

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.

[#3]

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]

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 · Accepted solution]

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]

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

[#8]

Thank you! Working well.