Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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...
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
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?
Use something like
...
ceil(Recno(),100) as Group,
...
Thanks swuehl..
ceil(RowNo() / 100)*100 as Group
This is also working..