Grouping data in a list

[#1]

Hi,

I have an excel file which has a column for lines of businesses. In that column I have 12 different values; however, when the data loads into Qlikview, I want to reclassify the list into 5 major lines of business. For example:

Original Source	Desired Qlikview Grouping
Asset Management	AM
Asset & Wealth Management	AM
Chief Operating Office	CIB
Chief Investment Office	CIB
Commercial Bank	CCB
Consumer and Community Bank	CCB
Corporate & Investment Bank	Corporate
Corporate Aligned	Corporate
Corporate Staff	Corporate
External	Corporate
Private Equity	Equity
Total Private Equity	Equity

Can I do this from the script? And/or, after loading the list?

Any help is appreciated, thank you!

[aarkay29]

MAY BE THIS

IF(MATCH([Original Source],'Asset Management','Asset & Wealth Management'),'AM',

IF(MATCH([Original Source],'Chief Operating Office','Chief Investment Office'),'CIB',

IF(MATCH([Original Source],'Commercial Bank','Consumer and Community Bank'),'CCB',

IF(MATCH([Original Source],'Corporate & Investment Bank','Corporate Aligned','Corporate Staff','External'),'Corporate',

IF(MATCH([Original Source],'Private Equity','Total Private Equity'),'Equity'

)))))    AS DESIREDGROUPING

OR USE THE ABOVE AS A CALCULATED DIMENSION

[Anonymous]

Just load the table you've gave and use it in "LEFT JOIN" to your main data, then drop it.

[Anil_Babu_Samineni]

Can you explain expected output from 12 lines

[jonathandienst]

I suggest that you create a mapping table and use ApplyMap() like this:

MAP_QVGROUPS:

Mapping LOAD * Inline

[

Original, Desired

Asset Management, AM

Asset & Wealth Management, AM

...

Total Private Equity, Equity

];

FactTable:

LOAD ...

  Source as [Original Source],               // if you want to keep original

  ApplyMap('MAP_QVGROUPS', Source, Source) as [Qlikview Grouping], 

  ...

The mapping table can be in-line loaded, or can come from an external source (spreadsheet, sharepoint, database etc).

If you want to highlight unmapped groups, then use something like

  ApplyMap('MAP_QVGROUPS', Source, 'Unmapped') as [Qlikview Grouping], 

[Anonymous]

For this create Inline table and map with Original Source Column

Hope it helps!!

[Anonymous]

Hi

Please find attached application. If not this, let us know your required output

Hope it helps!!

[#8]

Thank you so much Aar Kay, that worked!

[#9]

Thank you Anil, I was able to apply Aar Kay's suggestion. Being new to Qlikview it definitely helps to learn various ways of achieving the same result so really appreciate your help as well.

[Anonymous]

Please close the thread if you got the solution by marking Helpful or Correct Answers