Group the data

[#1]

Hello

I've a field i.e LKey it's data is from 1 to 1000 and another field DType i.e Edgechip,hole . Now i want to generate the table while loading in the below format. Is it possible? Can anyone help me or give idea

Group is main important here. i.e

Group

1     ->  1 to 100  

2     ->  101 to 200

3     ->  201 to 300

..

..

10    -> 901 to 1000

Group	DType	count(DType) of lens	Total lens
1	Edgechip	10	100
1	hole	5	100
2	Edgechip	9	200
2	hole	6	200

[swuehl · Accepted solution]

First create Group as new field using e.g.

LENSINPUT:

LOAD

LKey,

DType,

ceil(LKey / 100) as Group

from Table;

Then you can group by and do the counts.

RESULT:

LOAD Group, DType, count(DType), count(LKey) resident LENSINPUT group by Group,DType;

drop table LENSINPUT;

edit:

Not quite sure how your second count should work, maybe you could also look into

RESULT:

LOAD Group, DType, count(DType) resident LENSINPUT group by Group,DType;

Left Join LOAD Group, count (LKey) resident LENSINPUT group by Group;

drop table LENSINPUT;

[#3]

But it is not giving exact Group value...

Here i'm attaching my qvd file can you please try...

LENSINPUT:

LOAD LensKey,

DefectName,

ceil(LensKey / 100) as Group

FROM

LENSINPUT.qvd

(qvd)

;

RESULT:

LOAD Group, DefectName, count(DefectName) as CountDefectName resident LENSINPUT group by Group,DefectName;

//DROP Table LENSINPUT;

In table box it shows:

LensKey	Group	DefectName
1879590441	18795905	Lens.Missing
1879590442	18795905	Lens.Missing
1879590443	18795905	Defect.Bubble.Single
1879590444	18795905	Defect.None.Identified
1879590445	18795905	Defect.Bubble.Single
1879590446	18795905	Defect.Bubble.Single
1879590447	18795905	Defect.Bubble.Single
1879590448	18795905	Defect.Bubble.Single
1879590491	18795905	Defect.None.Identified
1879590492	18795905	Defect.Bubble.Single
1879590493	18795905	Defect.Bubble.Single

- I kept Lenskey ins Asc order.

- Group value shows the different values instead of 1,2,3...

[#4]

Sorry....Instead of Lenskey if i take RowNo() i'm getting what i want. But now what i want is instead of getting like

1 to 100 -> 1

101 to 200 ->2

I want

1 to 100 ->100

101 to 200 -> 200

[swuehl]

Hm,

you said LKey is data from 1 to 1000 and you want to group it in 'boxes' of 100 each

Group

1     ->  1 to 100  

2     ->  101 to 200

3     ->  201 to 300

..

..

10    -> 901 to 1000

That's what the code does, but since your LensKey values are much larger, your Group values also get larger.

A LensKey like 1879590441, what Group value should it be assigned to, then?

[swuehl]

Use something like

...

ceil(Recno(),100) as Group,

...

[#7]

Thanks swuehl..

ceil(RowNo() / 100)*100 as Group

This is also working..