Pivottable +1 for each row in a table

[#1]

I have a table that looks like:

BRANCH     COSTUMERNR     SALES     YEAR     BUYS     OLD/NEW   

---------------------------------------------------------------------------------------------

703                    4199037               49,5          2012          1               old

703                    1499038               500           2012          5               new

705                    3323612               150           2012          3               old

705                    4469257               60             2012          2               new

Now I  want to create a pivot table that has the dimension OLD/NEW and then I want to sepperate my cosumters in VIP, Regular ...., and then I want to count all my VIP costumers. A VIP costumer is someone who hast more than 4 BUYS and more than 500 SALES.

My Pivot table should look like:

               VIP     REGULAR

OLD          0          2

NEW         1          1

so basicly what I'm trying to do is to say I have 0 OLD VIP costumers , 2 OLD REGULAR costumers, 1 NEW VIP costumer and 1 NEW REGULAR costumer.

I hope someone can help me

regards

[#2]

Hey,

I'd do this by using flags in the script, something like this:

DATA:
LOAD * INLINE [
BRANCH, COSTUMERNR, SALES, YEAR, BUYS, OLD/NEW    
703, 4199037, 49.5, 2012, 1, old
703, 1499038, 500, 2012, 5, new
705, 3323612, 150, 2012, 3, old
705, 4469257, 60, 2012, 2, new
];
LEFT JOIN (DATA) LOAD
          *,
          if(_flagVIP=0, 1, 0) as _flagREG;
LOAD
          COSTUMERNR,
          if(SALES>499 AND BUYS>4, 1, 0) as _flagVIP
RESIDENT DATA;

Then just create a pivot with OLD/NEW as dimension and "sum(_flagVIP)" as one expression and "sum(_flagREG)" as another.

Good luck!

/F

[deepakk]

Hi,

I hope the attach filed helps you out.

Deepak