Pivot tables with 2 attributes for columns

ivangeraskin

I have table of purchases. Buyers with related Card_IDs buy diffrent products with related Segment (Cheap, Expensive) and Type (Meat, Bread, Milk)
Card_ID - Segment - Type - Amount
1012      - Cheap     - Meat - 1

1012      - Cheap     - Bread - 2

1013     - Expensive - Milk - 1

1013     - Cheap    - Bread - 3

1014     - Expensive - Bread - 1

1014     - Cheap    - Meat - 2

1014     - Expensive - Milk - 1

I want to make a pivot table, where "Card_ID" will be in string, "Segment"+"Type" in columns, aggregation sum by "Amount":
Card_ID - Cheap+Meat - Cheap+Bread - Cheap+Milk - Expensive+Meat - Expensive+Bread - Expensive+Milk

1012      -           1           -           2          -          0          -             0             -             0                -              0           

1013      -           0           -           3          -          0          -             0             -             0                -              1           

1014      -           2           -           0          -          0          -             0             -             1                -              1

Thank you for your help,
Ivan         

MartinLiebig

Hi,

 

i think the easiest thing is to create a new attribute with concat(Segment,Type) and use this as the index variable.

 

~Martin

ivangeraskin

Thank you very much!

ivangeraskin

Can you tell, which operator could make such pivot table (after concatination), aggregating by sum (Card_id in strings, Segment_Type in columns)? I can't make "pivot table" operator do such thing.

MartinLiebig

Hi,

 

you first use Generate Attribute to generate the Concat, then Select Attributes to remove the two attributes you just concatted and then Pivot.

 

~Martin

ivangeraskin

Thank you) I found solution to aggregate by sum we also need Aggregate operator before Pivot Table operator.

ivangeraskin

Thank you) I found solution, to aggregate by sum we also need Aggregate operator before Pivot Table operator.