topic Getting frequency from data in QlikView

Getting frequency from data

Mon, 03 Jan 2011 08:30:52 GMT

In the attached sample data need frequency of the categories.

The category definition is in the "category" worksheet of attached file. Need to calculate number of records in in each category from the data column.

Tried with Count function but it is executing only once.

Please provide some suggestion.[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/5355.Sample1.xls:550:0]

Getting frequency from data

Mon, 03 Jan 2011 10:36:43 GMT

Hi,

please see the attached example.

Hope it´s what you are looking for.

Good luck!

Rainer

Getting frequency from data

Mon, 03 Jan 2011 10:42:08 GMT

You could do it by adding the category as an extra field in your table while loading the data.

See attached file for what i mean.

Regards,

Yves.

EDIT: as i cannot upload my example file, here is the script:

EDIT2: managed to upload the example with IE

Getting frequency from data

Mon, 03 Jan 2011 11:16:32 GMT

Can you please copy paste your code . As I'm using personal edition can't open your file(s).

Thanks a lot for your support in advance.

Getting frequency from data

suniljain — Mon, 03 Jan 2011 11:25:38 GMT

If (Data <3, 'category 1',
If (Data <9 and Data >= 3, 'category 2',
If (Data >= 9 and Data <30, 'category 3', 'category 4'))) as Category

Getting frequency from data

Mon, 03 Jan 2011 11:35:32 GMT

Here you are,

Temp:
LOAD * INLINE [
Id, Data
1, "2,5"
2, "4,8"
3, "1,9"
4, "11,3"
5, "23,8"
6, "6,9"
7, "21,4"
8, "17,3"
];

Cat:
Load
Id,
If (Data <3, 'category 1',
If (Data <9 and Data >= 3, 'category 2',
If (Data >= 9 and Data <30, 'category 3', 'category 4'))) as Category
Resident Temp;

Rainer

Getting frequency from data

Mon, 03 Jan 2011 12:05:14 GMT

Thanks for the update but actually I'm looking for the num of occurances of "category 1" , category 2 e.t.c .

Means how many data belongs to diferent category - say 3 occurances of cat 1, 4 of cat2 , 2 of cat3 and 1 of cat4.

Getting frequency from data

Mon, 03 Jan 2011 14:01:48 GMT

Hi,

please put the LOAD statements in your script editor.

Next create the charts and you will get what you want (see the screen schots).

Rainer

Getting frequency from data

Mon, 03 Jan 2011 17:09:55 GMT

DATA:
LOAD
Data,
if( Data < 3,
'Category 1',
if( Data < 9,
'Category 2',
if( Data < 30,
'Category 3',
'Invalid'
)
)
) as Category,
1 as Counter
FROM[5355.Sample1.xls](biff, embedded labels, table is data$);

That's the code I used. Result is the same as Rainer, only with this, you'll only have 1 table.

then create a table with 'Category' as Dimension and 'sum(Counter)' as expression.

Regards,

Yves.