topic Re: Merge Rows in QlikView

Merge Rows

Fri, 17 Jun 2016 16:28:30 GMT

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‌

Re: Merge Rows

sunny_talwar — Fri, 17 Jun 2016 17:37:58 GMT

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

Data Cleansing

Re: Merge Rows

santiago_respane — Fri, 17 Jun 2016 17:53:29 GMT

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,

Re: Merge Rows

antoniotiman — Fri, 17 Jun 2016 18:04:38 GMT

Try Calculated Dimension

Aggr(MinString(CustName),CustNo)

Re: Merge Rows

stevedark — Mon, 20 Jun 2016 14:55:36 GMT

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