Getting frequency from data

[#1]

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]

[#2]

Hi,

please see the attached example.

Hope it´s what you are looking for.

Good luck!

Rainer

[#3]

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

[#4]

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.

[suniljain]

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

[#6]

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

[#7]

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.

[#8]

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

[#9]

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.