Group Alphabets into Buckets

[dmohanty]

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!

[rwunderlich]

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

[dmohanty]

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!

[rwunderlich]

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

[Saravanan_Desingh]

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; 

[Brett_Bleess]

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