Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Grouping data in a list

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 SourceDesired Qlikview Grouping
Asset ManagementAM
Asset & Wealth ManagementAM
Chief Operating OfficeCIB
Chief Investment OfficeCIB
Commercial BankCCB
Consumer and Community BankCCB
Corporate & Investment BankCorporate
Corporate AlignedCorporate
Corporate StaffCorporate
ExternalCorporate
Private EquityEquity
Total Private EquityEquity

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

Any help is appreciated, thank you!

11 Replies
aarkay29
Specialist
Specialist

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
Not applicable
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

For this create Inline table and map with Original Source Column

Community.jpg

Hope it helps!!

Anonymous
Not applicable
Author

Hi

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

Hope it helps!!

Not applicable
Author

Thank you so much Aar Kay, that worked!

Not applicable
Author

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
Not applicable
Author

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