topic Pivot table and grouping data in QlikView

Pivot table and grouping data

Tue, 26 Apr 2011 04:30:41 GMT

I have a simple data but can't figure out on how to put the data together. This is what I have:

Location

Days

Count

Texas

I want to group the Days by duration, here are the duration description:

Duration

At the same day

<=0

Next day

<=1

More than 1 day

This is what I want to accomplish with pivot table:

Location

Duration

Count

Texas

At the same day

Next day

More than 1 day

I've been browsing this forum but can't find any answer.

Any help is appreciated! Thank you.

Pivot table and grouping data

Tue, 26 Apr 2011 06:21:11 GMT

Hi,

Data:
LOAD * INLINE [
Location, Days, Count
Texas, 0, 10
Texas, 1, 6
Texas, 4, 3
];

Let vToday = 0;
Let vTodayMax= peek('Days',-1,Data);
DaysRange:
LOAD
Range,
Evaluate(Low)AS Low,
Evaluate(High) AS High
;
LOAD * INLINE [
Range; Low; High
At the same day;vToday ; vToday
Next day; vToday; vToday+1
More Than 1 day; vToday+1; vTodayMax
] (delimiter is ';')
;

JOIN(DaysRange) IntervalMatch(Days) Load Low, High Resident DaysRange ORDER BY Low ASC;

after running this script, create a pivot table with dimension as location,ranges and expresion as sum(count).

It solves your problem...

Niranjan M.

Pivot table and grouping data

rahulgupta — Tue, 26 Apr 2011 07:11:27 GMT

Hey Hi Dear, I have attached a test file as per your data.... Hope this contributes in solving your issue..!! Please have a look and let me know any further issues....!!

Pivot table and grouping data

gauravkhare — Tue, 26 Apr 2011 07:24:47 GMT

Hi Buddy,

from your requirment is for certain that you need to have 2 dimensions namely location and duration and the expression will the count of something.....

Now location we have and for the duration purpose you can do one thing... either go for calculated dimension or create the same in the script part itself..

Goto add calculated dimension tab and in that write the script like- if(Days>1,'greater than 1',

if(Days>4,'greater than 4', etc etc))..

so now you have got two dimensions one is city and the second is Duration..Take these two dimensions in your pivot table and take expression as count of days.

Hope you must have got an idea on how to proceed if in case you face any problem kindly send me a sample data and I will create an application for you.