Merge Rows

[#1]

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.

My final output should be:

Thank you.

jontydkpi‌ gwassenaar‌ stevedark‌ hic‌

[sunny_talwar]

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

Data Cleansing

[santiago_respane]

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]

Try Calculated Dimension

Aggr(MinString(CustName),CustNo)

[stevedark]

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