Planning Analytics with Watson

 View Only
  • 1.  Removing duplicate elements when pasting in the Set editor in PAX

    Posted Mon February 28, 2022 09:44 AM
    Hello,

    When copying text from an excel sheet/quick report and pasting in the set editor we get duplicated elements if in the copied selection there are more than one occurrence of the same element. Is there an way to removing the duplicates in the set editor and keeping only one instance of each unique element? 

    I could swear that this was a normal behavior before but now users are reporting that pasting in the set editor is generating a lot of duplicates of the same element. Thanks.

    ------------------------------
    Luiz gustavo Ribeiro
    ------------------------------


  • 2.  RE: Removing duplicate elements when pasting in the Set editor in PAX

    Posted Mon February 28, 2022 12:28 PM

    Hi,
    By default the subset editor does not remove duplicates.  In perspectives, if you use the set editor and choose hierarchy sort, it removes duplicates as long as the element does not show up in the hierarchy more than once.
    In PAW/PAX subset editor you need to do a work around.  If you switch to MDX mode, and add the following it should get rid of duplicates:
    INTERSECT( [dimension name].MEMBERS, {Rest of the existing MDX code } )
    Another work around is to paste the elements into a new tab in excel and choose the remove duplicates.  Then copy it again and paste into the subset editor.



    ------------------------------
    Scott Brown
    ------------------------------



  • 3.  RE: Removing duplicate elements when pasting in the Set editor in PAX

    Posted Tue March 01, 2022 03:30 AM
    Hi Luiz, Scott,

    If you are switching to MDX already, as Scott suggests, you can simply put a DISTINCT( {Rest of the existing MDX code} ) around it which removes any duplicates from the given set while retaining the first instance of each element from that original set.

    ------------------------------
    Hubert Heijkers
    ------------------------------



  • 4.  RE: Removing duplicate elements when pasting in the Set editor in PAX

    Posted Wed March 02, 2022 12:00 PM
    Keep in mind though DISTINCT({MDX Statement}) is not the same as starting with TM1SubsSetAll. Distinct is looking for unique MUNs (Member Unique Name).

    [Product].[Product].[New Products^A] is a different member than [Product].[Product].[Products On Sale^A]

    ------------------------------
    Ryan Clapp
    ------------------------------



  • 5.  RE: Removing duplicate elements when pasting in the Set editor in PAX

    Posted Fri March 04, 2022 06:29 PM
    Hello,

    Why does PAW think this is an invalid MDX ?

    {Distinct( {[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]} )}

    The dimension is called "Wim_"
    The hierarchy is called "Rolling 12 periods"
    The same element "202101" rolls up into "Rolling 12 periods Jan 2021" and "Rolling 12 periods Feb 2021"
    Without Distinct( ) it correctly returns 2 elements. With Distinct() I get "Invalid MDX expression".
    I would expect to have PAW return the set with the same 2 elements (same name, different MUN).

    ------------------------------
    Wim Gielis
    ------------------------------



  • 6.  RE: Removing duplicate elements when pasting in the Set editor in PAX

    Posted Sat March 05, 2022 12:41 PM

    Hi Wim,

    That would be because you are passing two parameters, two sets in this case, to the Distinct function whereas Distinct only accepts one parameter of type set.
    I'm guessing you started out with a set expression like:

    {{[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]}}

    which already is a  union and as such the same as:

    Union({[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]}, ALL)

    and had you just wrapped that expression with the Distinct function as in:

    Distinct({{[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]}})

    which therefore is the same as:

    Distinct(Union({[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]}, ALL))

    That would have been fine. Adding additional sets of curly braces is allowed but unnecessary and the execution engine will unfold it effectively.

    But the real question I guess is why you are create a set per member here in the first place? What you really are asking for is:

    Distinct({[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101], [Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]})

    That's all that's needed.

    Hope that helps!



    ------------------------------
    Hubert Heijkers
    ------------------------------



  • 7.  RE: Removing duplicate elements when pasting in the Set editor in PAX

    Posted Sat March 05, 2022 12:58 PM
    Thanks Hubert !
    Indeed, I must have been fighting with the curly braces.

    ------------------------------
    Wim Gielis
    ------------------------------



  • 8.  RE: Removing duplicate elements when pasting in the Set editor in PAX

    Posted Fri March 25, 2022 09:04 AM
    Luiz - you can use the hierarchy sort button to remove duplicates. You do not mention it in your post, but I believe if the user is coping from a filtered excel list, all values hidden by the excel filter will get copied as well which can result in a similar issue. I find the best way around this is to copy the filtered excel list and paste it in a blank workbook where you can see that there are no hidden rows. Then copy that clean list and pasted it into PAX set editor.

    ------------------------------
    Matthew Manuele
    ------------------------------