How to Group Data

[#1]

I have an excel spreadsheet with about 500,000 rows of data. One particular column title, "PERSONGROUP", has about 50-60 different entries, but several of them mean the same thing (i.e. 'FAC', 'FAC1', 'FAC2' should all be called 'FAC', 'ES1', 'ES2', 'ES3' should all be called 'ES', and so on). Would the best thing to do be write some code in the qlikview script, make changes in the excel file, or something else entirely? My coding experience is very limited, so the simpler the solution the better...

[vishsaggi]

Can you share a dummy excel file with few combinations as there are different combination of groups i believe you have to consider all the different groups and tie them accordingly. I assume we need to know how many groups you have to consider as mentioned in the other thread, use Inline table posted by Gysbert and give all the relevant combinations.

[swuehl · Accepted solution]

Maybe have a look at

Data Cleansing

So you can use a mapping table approach, or maybe it's just enough to drop the numeric digits in your field values?

LOAD

     Purgechar(PERSONGROUP, '0123456789') as PERSONGROUP,

     ...

[soloeeeoff]

Hi Jacob,

Glad to see a requirement as I faced.

It can be solved by using the Mapping.

If you please give me a sample Data identical to what you need, you need can be fulfilled.

Thanks.