Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Merge Rows

Hi,

I would appreciate if someone would be able to help me figure this out. I will like to have just one CustName because they have the same CustNo. I wan to match/merge the two into one (Example: Instead of having the two, I want just one and to say "CitiB") and still have the same records in my straight table. Logically, I want to convert CitiB-City to CitiB since they both have the same CustNo. Please see attachment for sample .qvw file.

test_merge.JPG

My final output should be:

test_merge2.JPG

Thank you.

jontydkpigwassenaarstevedarkhic

4 Replies
sunny_talwar

I think you need to cleanse your data in the back end to be able to do this:

Data Cleansing

santiago_respane
Specialist
Specialist

Hi Omosalewa,

i agree with Sunny here that you may need some data cleansing, but as an alternative you can have a list with the mapping (CustNo and CustMergedName) for all items to be merged; And use it with the applymap function. This list must be maintained with every CustName that you want to merge.

Important: This may not be the best solution.

Attached you will find an example.

Kind regards,

antoniotiman
Master III
Master III

Try Calculated Dimension

Aggr(MinString(CustName),CustNo)

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Agree with the other comments regarding cleansing your data in the back end, but this is not difficult to fix regardless.

When loading your main data file, only load the number and not the name, and ApplyMap on the name.

This way only one name will be associated with each number (an arbitrary one, but at least it will only be one).


Create the mapping table like this:

Map_CustName:

MAPPING LOAD DISTINCT

  CustNo,

  CustName

FROM SourceFile.qvd (qvd);

And then use that mapping table like this:

MainData:

LOAD
  CustNo,

  ApplyMap('Map_CustName', CustNo) as CustName

  ... rest of your fields ...

FROM SourceFile.qvd (qvd);

For a full description of why this works, and more about ApplyMap, you could read up on this blog post:

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Hope that helps,

Steve