Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
I hope the attach filed helps you out.
Deepak