topic Group Alphabets into Buckets in QlikView

Group Alphabets into Buckets

dmohanty — Wed, 06 May 2020 18:56:13 GMT

Hi Team,

This might look simple, but somehow stuck here -

I have a USERS column having names of the Users, with first name starting from A-Z. From this column, I created a calculated field in script called 'Alphabets' by 'Left (USERS, 1) as Alphabets', which gave me 26 distinct values from A-Z.

Now I want to group the alphabets into 13 buckets in another column called 'Bucket' like, A&B in 1 , C&D in 2, E&F in 3..... Y&Z in 13.

So the new field Bucket should have 13 distinct values that would have proper association.

Please assist here.

Regards!

Re: Group Alphabets into Buckets

rwunderlich — Wed, 06 May 2020 21:57:57 GMT

An easy way would be to type up a Mapping table like this:

BucketMap:
Mapping Load * Inline [
alpha, bucket
A, A&B
B, A&B
C, C&D
...etc...
];

And then use the mapping table in your load. I find coding more fun than typing so here's an example with generating the mapping table and testing it:

BucketMap:
Mapping
LOAD
chr(64+RecNo()),
if(Odd(RecNo())
,chr(64+RecNo()) & '&' & chr(64+RecNo()+1)
,chr(64+RecNo()-1) & '&' & chr(64+RecNo())
)
AutoGenerate 26;
MAP Bucket using BucketMap;

Alpha:
LOAD
*,
Alpha as Bucket;
LOAD
chr(64+RecNo()) as Alpha
AutoGenerate 26;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Group Alphabets into Buckets

dmohanty — Fri, 08 May 2020 13:31:42 GMT

Hi @rwunderlich

Thanks for sharing an interesting idea. Yeah this helped as well.

Even I could have used an Inline table to join, but wanted a reusable idea on this. So I was trying this concept, but somehow didnt worked out -

If(Substringcount(Concat( DISTINCT Alphabets, '&') , 'A&B') = 1 , 'A&B' ,

If(Substringcount(Concat( DISTINCT Alphabets, '&') , 'C&D') = 1 , 'C&D'

...... ) as Bucket

Resident TableName;

Is this something we can tweak the above to make it work ?

Regards!

Re: Group Alphabets into Buckets

rwunderlich — Fri, 08 May 2020 14:50:49 GMT

A method that would not use another table might be:

if(Odd(ord(Alpha))
,Alpha & '&' & chr(Ord(Alpha)+1)
,chr(Ord(Alpha)-1) & '&' & Alpha
) as Bucket

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Group Alphabets into Buckets

Saravanan_Desingh — Fri, 08 May 2020 16:17:28 GMT

To add to this discussion...

BucketMap: Mapping LOAD chr(64+RecNo()), if(Odd(RecNo()) ,chr(64+RecNo()) & '&' & chr(64+RecNo()+1) ,chr(64+RecNo()-1) & '&' & chr(64+RecNo()) ) AutoGenerate 26; MAP Bucket using BucketMap; Data: LOAD *, Left(Name,1) As Bucket; LOAD Capitalize(KeepChar(Hash128(Rand()),'ABCEDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')) As Name AutoGenerate 20;

Re: Group Alphabets into Buckets

Brett_Bleess — Thu, 28 May 2020 20:04:50 GMT

You have received two follow-up posts to your initial request, we would appreciate it if you would be sure to return to the post and close the thread if you have a solution, and if the last two posts did help, be sure to use the Accept as Solution button to mark the post(s) that helped, as this gives them credit for the assistance and lets other Members know what worked. If you have further questions etc., please leave an update post.

Regards,
Brett