Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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!
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
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;
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