Grouping code

[Anonymous]

Hi All,

I have below codes filed CODE and I want to create grouping for that

1000100

1000101

1000102

1000103

1000104

1000200

1000201

1000202

1000203

1000204

expected output...

GorupCode	Code
1000100	1000100
	1000101
	1000102
	1000103
	1000104
1000200	1000200
	1000201
	1000202
	1000203
	1000204

Thanks

[marcus_sommer]

Try it with:

floor(Code / 100) * 100 as GroupCode

- Marcus

[swuehl]

Or maybe just

Floor( Code, 100) as GroupCode

[surendraj]

Try this

load *,if(GroupCode=Previous(GroupCode),' ',GroupCode) as Group;

LOAD distinct floor(code / 100) * 100 as GroupCode,* INLINE [

    code

    1000100

    1000101

    1000102

    1000103

    1000104

    1000200

    1000201

    1000202

    1000203

    1000204

];

Apply some sortings in sort tab.

[swuehl]

Surendra, this solution might visually group codes, but the data loses the relation for most records, i.e. Code 10000102 is not related to Group 10000100.

If you want to visually group the codes, it's better to use e.g. a pivot table.

edit:

Sorry, I've missed that you are keeping the field GroupCode in the model using the star symbol.

Hence the relation in your data would still be available.

[surendraj]

Try this

LOAD distinct floor(code / 100) * 100 as GroupCode,* INLINE [

    code

    1000100

    1000101

    1000102

    1000103

    1000104

    1000200

    1000201

    1000202

    1000203

    1000204

];

Take a pivot table--dimensions-->take Group code,Code

expressions-->

Presentation-->

Uncheak the Supress Zero-Values.

output will be: