Forum Discussion

eldarkt's avatar
eldarkt
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago
Solved

Filter by multiple sets of column

Hi all, I have a data set like this:   Customer1_value1 Customer1_value2 … Customer1_value15 Customer2_value1 … Customer2_value15 … CustomerN_value1 … CustomerN_value15 0.229151652 ...
  • sdjensen's avatar
    sdjensen
    9 years ago

    Hi eldarkt,

     

    This approach should do the trick

    1. select the column 'Attribute.2'

    2. Select 'Pivot Column' on the Transform tab

    3. In the pop-up select 'Value' as Values Column

eldarkt's avatar
eldarkt
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Thanks, sdjensen!

Now I have this:

DatetimeAttribute.1Attribute.2Value
datetime1Customer1_value10.229151652
datetime1Customer1_value20.155057721
datetime1………
datetime1Customer1_value150.324584312
datetime1Customer2_value10.110479662
datetime1Customer2_value20.16026891
datetime1………
datetime1Customer2_value150.090275502
datetime1CustomerN_value10.967682291
datetime1CustomerN_value20.493830084
datetime1………
datetime1CustomerN_value150.48551334
datetime2Customer1_value10.547241
datetime2Customer1_value20.767585491
datetime2………
datetime2Customer1_value150.906074292
datetime2Customer2_value10.443983587
datetime2Customer2_value20.223704644
datetime2………
datetime2Customer2_value150.395398869
datetime2CustomerN_value10.59960649
datetime2CustomerN_value20.86645009
datetime2………
datetime2CustomerN_value150.221637108
datetime3Customer1_value10.895628899
datetime3Customer1_value20.814316364
datetime3………
datetime3Customer1_value150.949635827
datetime3Customer2_value10.101984896
datetime3Customer2_value20.977557759
datetime3………
datetime3Customer2_value150.922149649
datetime3CustomerN_value10.059627022
datetime3CustomerN_value20.234168244
datetime3………
datetime3CustomerN_value150.065165743
…………


Do you know is it possible to convert it to structure described (under "to this:") in my previous post?
I tried "pivot", it didn't work...

Thanks again for your help!

sdjensen's avatar
sdjensen
Icon for Solution Sage rankSolution Sage
9 years ago

Hi eldarkt,

 

This approach should do the trick

1. select the column 'Attribute.2'

2. Select 'Pivot Column' on the Transform tab

3. In the pop-up select 'Value' as Values Column

  • eldarkt's avatar
    eldarkt
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

     

    Many thanks, sdjensen, it works!

    My mistake when I tried pivot was I didn't turn off aggregation here (it was "count(all)" by default) :


    and it showed "1" in each field instead of source values.
    I changed it to "Don't Aggregate" and it did the trick.
    What a wonderful forum it is! :smileyhappy: