Summarize multiple fields with intersection
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I'd like to summarize my data by 5 different fields and add an element to each field "All" that contains the summarized data, which I have mostly done (sample workflow attached). The issue I'm having is that the "All"s don't intersect. For example,
Source | Month | Store | Product | Segment | Device | Payment Type | Value 1 | Value 2 |
sys | 2016-02-01 | Merchandise | Basic | Standard | Mobile | Credit | 1 | 1 |
sys |
2016-02-01 |
Gift | Basic | Standard | Tablet | Cash | 1 | 1 |
sys | 2016-02-01 | All | Basic | Elite | Tablet | Cash | 1 | 1 |
sys | 2016-02-01 | All | Basic | Standard | Mobile | Cash | 2 | 2 |
sys | 2016-02-01 | Gift | All | Elite | Tablet | Cash | 1 | 1 |
sys | 2016-02-01 | Gift | All | Elite | Tablet | Credit | 1 | 1 |
This works fine if I only want to see "All" Store broken down by the various Products, Segments, etc. But if I want to see "All" Store and "All" Product by Segment, Device and so on, it doesn't work since there is only one "All" per row (the "All"s don't intersect at the row level). I'm not quite sure how to explain this more clearly, so my apologies for any confusion. Thanks for the help.
Solved! Go to Solution.
- Labels:
- Preparation
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for your response. That seems to work as needed, and is much simpler than what I was originally trying to do.
I don't quite understand all of the steps you've taken, particularly how you determined 32 sets and the use of "BinaryAnd". Would you mind explaining briefly?
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I used a binary mask. You have five fields so I needed 2^5 ==> 32 numbers
Then looking at the binary representation of each number I create a mask:
If the first bit is 1 (i.e. BinaryAnd(x, 1) == 1) then set Field1 to 'All'
If the second bit is 1 (i.e. BinaryAnd(x, 2) == 2) then set Field2 to 'All'
...
This creates 32 masked sets of data (well 31 plus one with no masking).
Hope that makes some sense
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I knew I was going to feel dumb after asking about the 32 sets.
Thanks for the explanation.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Not at all, a sneaky trick to create a set of masks