topic Grouping data in a list in QlikView

Grouping data in a list

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Grouping data in a list

aarkay29 — Mon, 12 Jun 2017 21:56:27 GMT

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

Re: Grouping data in a list

Anonymous — Tue, 13 Jun 2017 00:56:10 GMT

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

Re: Grouping data in a list

Anil_Babu_Samineni — Tue, 13 Jun 2017 01:59:25 GMT

Can you explain expected output from 12 lines

Re: Grouping data in a list

jonathandienst — Tue, 13 Jun 2017 04:43:26 GMT

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],

Re: Grouping data in a list

Anonymous — Tue, 13 Jun 2017 04:44:37 GMT

For this create Inline table and map with Original Source Column

Hope it helps!!

Re: Grouping data in a list

Anonymous — Tue, 13 Jun 2017 04:55:07 GMT

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

Hope it helps!!

Re: Grouping data in a list

Tue, 13 Jun 2017 14:33:21 GMT

Thank you so much Aar Kay, that worked!

Re: Grouping data in a list

Tue, 13 Jun 2017 14:35:45 GMT

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.

Re: Grouping data in a list

Anonymous — Tue, 13 Jun 2017 16:39:26 GMT

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

Re: Grouping data in a list

albertovarela — Tue, 13 Jun 2017 16:58:25 GMT

You may find this helpful: Don't join - use Applymap instead

Re: Grouping data in a list

passionate — Tue, 13 Jun 2017 17:20:57 GMT

Hi Azdar,

You can achieve this by:

1. ApplyMap

2. Replace Function in Load Script

3. Using Match as suggested by Kay But, Modify Using Pick Match rather than If.

Regards,

Pankaj