5 Replies Latest reply: Sep 29, 2016 12:17 PM by Matt Melhus RSS

    Create Filter

    Matt Melhus

      Hello,

       

      I'm trying to create a filter where the following condition is met:  Ability to identify all Item Types where the Item Type is = A and the ID doesn't have multiple Item Types associated to it. 

       

      ID   Item Type

      1     A

      2     B

      3     A

      3     B

      3     C

      4     A

      4     B

      5     C

      6     A

       

      In this case, I'd like for it to return

       

      ID

      1

      6

       

      Currently, I can use Item Type as a filter but if I select A it includes instances where other Item Types were associated to the same ID. If anyone has any suggestions that would be greatly appreciated. 

        • Re: Create Filter
          Sunny Talwar

          Is this needed for selection or are you looking to create a chart where ID 1 and 6 are displayed? I guess provide some context of where it is needed to be used and may be we can offer some useful advice

            • Re: Create Filter
              Matt Melhus

              Sorry.  Yes, I need to be able to create a selection that will then only display ID 1 and 6 on a chart.   Currently, when I use a standard filter for Item Type it would return IDs for 1, 3, 4, and 6.  

                • Re: Create Filter
                  Sunny Talwar

                  May be create a flag in the script:

                   

                  Table:

                  LOAD ID,

                             [Item Type]

                  FROM...

                   

                  Left Join (Table)

                  LOAD ID,

                             Count(DISTINCT [Item Type]) as Count

                  Resident Table;

                   

                  May be now on the front end create a filter selection object using the following expression:

                  Aggr(Only({<Count = {1}, [Iten Type] = {'A'}>} [ID]), [ID])

              • Re: Create Filter
                Matt Melhus

                Sonny -

                 

                I was able to get the load script but had to add a group by ID so it didn't return an invalid expression error during the load.  Thanks for your help!