topic Grouping code in QlikView

Grouping code

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Grouping code

marcus_sommer — Fri, 27 Jan 2017 08:04:29 GMT

Try it with:

floor(Code / 100) * 100 as GroupCode

- Marcus

Re: Grouping code

swuehl — Fri, 27 Jan 2017 08:19:48 GMT

Or maybe just

Floor( Code, 100) as GroupCode

Re: Grouping code

surendraj — Fri, 27 Jan 2017 08:24:40 GMT

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.

Re: Grouping code

swuehl — Fri, 27 Jan 2017 08:32:26 GMT

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.

Re: Grouping code

surendraj — Fri, 27 Jan 2017 09:08:28 GMT

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: