Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Not applicable

How do I group certain values of a field to one new value, within set-analysis, keeping the rest untouched?

Hi all,

I want to build an object based around "Marketing Sources" for leads that my company generates.

There are many possible values to this field, and I'd like to group some of them together when I build straight tables, charts, etc. Marketing Source is one of the dimension.

I'm using set analysis in my expression for regular purposes, for example:

Count ( { <[Date Diff]={">$(=Max({< [Date Diff]={'<=0'}>} [Date Diff])-11)"}, Status={'Lead CreatedDate'}, [Marketing Source]-={'Excluded_Source1''},[Marketing Medium]-={'Excluded_Medium1'}>}[Lead Id])

I assume I need to use calculated dimensions but I couldn't get it to work properly (i.e. show anything). Also, not sure how I can define in a calculated dimension to only group certain fields and keep the rest as is.

For example, I'd like to group:

'PPC Google', 'PPC Bing', 'PPC Other' to just 'PPC'

'Google','Bing' to just 'Search'

Would appreciate your help with this one!

Thanks!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: How do I group certain values of a field to one new value, within set-analysis, keeping the rest untouched?

May be like(calculated dimension):

=If( WildMatch([Marketing Source], 'PPC*' , 'PPC' , If(Match([Marketing Source], 'Google' , 'Bing') , 'Search' ,[Marketing Source]))

Note: The highlighted part keeps your values untouched if conditions don't match.

View solution in original post

3 Replies
Highlighted
MVP
MVP

Re: How do I group certain values of a field to one new value, within set-analysis, keeping the rest untouched?

May be like(calculated dimension):

=If( WildMatch([Marketing Source], 'PPC*' , 'PPC' , If(Match([Marketing Source], 'Google' , 'Bing') , 'Search' ,[Marketing Source]))

Note: The highlighted part keeps your values untouched if conditions don't match.

View solution in original post

Highlighted
Not applicable

Re: How do I group certain values of a field to one new value, within set-analysis, keeping the rest untouched?

exactly what I want say. Just replace your old dimension with the calculated one.

Or may be you can hard code an additional column in your load script, like 'Type', and decide PPC or Search there. Just another option.

Highlighted
Not applicable

Re: How do I group certain values of a field to one new value, within set-analysis, keeping the rest untouched?

Seems to work.

I was very close then. Not sure what went wrong.

Thanks both!