Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Pivottable +1 for each row in a table

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 Replies
Not applicable

Re: Pivottable +1 for each row in a table

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
Valued Contributor III

Re: Pivottable +1 for each row in a table

Hi,

I hope the attach filed helps you out.

Deepak

Community Browser