Renaming 2 fields to one common field name

[Anonymous]

Hi

I have 2 fields Primary Owner and Secondary Owner with incorrect names. Hence i have created a mapping table to map the incorrect values with the correct names.

I have used applymap('maptable',Primary Owner) as Owner field.

When I try to use applymap('maptable',Secondary Owner) as Owner it doesn't allow me to do it as this field already exists. has anybody faced a similar issue like this..IF there a workaround for this please let me know

[marcus_sommer]

Yes a field within a table must be unique - maybe you need something like this:

applymap('maptable',[Primary Owner], applymap('maptable',[Secondary Owner], '#NV')) as Owner

- Marcus

[swuehl]

How do you want to 'merge' your two fields into one?

Could you post some sample records and your expected resulting table?

[vinieme12]

can you post your script?

[swuehl]

See my answer here:

https://community.qlik.com/message/1138966?tstart=0#1138966

[Anonymous]

have pasted my sample file . File concatenates the extract from multiple sources i.e. RTC, Problems, Incidents and TR.

A mapping sheet has been created as the names are different in each of these extracts so as to map these to the actual names that are required.

In the RTC extract there are 2 fields i.e. Primary and Secondary Owner against one ticket. So each ticket is resolved by 2 people.  I have to put a list box with the names so when the user selects on a particular name the count of the tickets resolved by that person is calculated irrespective of the name appearing in the primary or secondary column should appear. The count should take into consideration both the cols i.e.Primary and Secondary

[Anonymous]

have pasted my sample file . File concatenates the extract from multiple sources i.e. RTC, Problems, Incidents and TR.

A mapping sheet has been created as the names are different in each of these extracts so as to map these to the actual names that are required.

In the RTC extract there are 2 fields i.e. Primary and Secondary Owner against one ticket. So each ticket is resolved by 2 people.  I have to put a list box with the names so when the user selects on a particular name the count of the tickets resolved by that person is calculated irrespective of the name appearing in the primary or secondary column should appear. The count should take into consideration both the cols i.e.Primary and Secondary

[swuehl · Accepted solution]

Have you tried with a link table as I suggested in the other thread?

Using your sample excel file, the count against names will look like

New Name	Count(DISTINCT [Ticket Number])
Bibhu Sharma	2
Erwin P	3
John F	2
Mary Kate	1
Sanjyot Patkar	2

Ticket Number	New Name
ABCD	John F
ABCD	Sanjyot Patkar
EFGH	Erwin P
EFGH	Mary Kate
IJKL	Erwin P
IJKL	John F
MNOP	Bibhu Sharma
MNOP	Erwin P
RSTU	Bibhu Sharma
RSTU	Sanjyot Patkar

You basically just need to create a table with Ticket Number as key field, then a single column with the names (by using CROSSTABLE LOAD prefix or just loading the source table twice, using primary names in first load and secondary names in second load and aliasing these fields to a canonical field name).

[Anonymous]

I used the linktable suggestion and it did work as long as there is a single load and there is no concatenation of another extract.

The moment I tried concatenating another extract eg. Problems  it starting generating synthetic keys.I have New Name field in the Problem dump extract created using applymap function.

I concatenate this extract with the above extract that uses the link table and see the synthetic keys under Ticket number and New Name field.

[swuehl]

It would be helpful if we could see your current script or outline of the actual table processing.

But basically, create your fact table first (concatenating your source tables if needed), then create your link table at the end, based on the already concatenated fact table.