Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Renaming 2 fields to one common field name

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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 Sharma2
Erwin P3
John F2
Mary Kate1
Sanjyot Patkar2

Ticket Number New Name
ABCDJohn F
ABCDSanjyot Patkar
EFGHErwin P
EFGHMary Kate
IJKLErwin P
IJKLJohn F
MNOPBibhu Sharma
MNOPErwin P
RSTUBibhu Sharma
RSTUSanjyot 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).

View solution in original post

10 Replies
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
MVP
MVP

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

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

vinieme12
Champion III
Champion III

can you post your script?


Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
swuehl
MVP
MVP

Anonymous
Not applicable
Author

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
Not applicable
Author

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
MVP
MVP

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 Sharma2
Erwin P3
John F2
Mary Kate1
Sanjyot Patkar2

Ticket Number New Name
ABCDJohn F
ABCDSanjyot Patkar
EFGHErwin P
EFGHMary Kate
IJKLErwin P
IJKLJohn F
MNOPBibhu Sharma
MNOPErwin P
RSTUBibhu Sharma
RSTUSanjyot 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
Not applicable
Author

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
MVP
MVP

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.