Duplicate company name

[#1]

Hi all,

I need help and advise with this one.

I have the data below but the code are the same but the company name are different so in my qlikview report its showing as two different companies when they are one company based on the CompCode

   current showing

CompCode	Comp Name	Wage
S123	CompanyName_1	£1,000.00
S123	CompanyName_12	£2,000.00
S124	CompanyName_3	£1,000.00
S124	CompanyName_31	£2,000.00
S125	CompanyName_4	£1,000.00
S125	CompanyName_41	£2,000.00
S126	CompanyName_5	£1,000.00
S126	CompanyName_51	£2,000.00
S127	CompanyName_6	£1,000.00
S127	CompanyName_61	£2,000.00
S128	CompanyName_7	£1,000.00
S128	CompanyName_71	£2,000.00
S129	CompanyName_8	£1,000.00
S129	CompanyName_81	£2,000.00
S131	CompanyName_9	£1,000.00
S131	CompanyName_91	£2,000.00
S132	CompanyName_0	£1,000.00
S132	CompanyName_01	£2,000.00
S133	CompanyName_02	£1,000.00
S133	CompanyName_021	£2,000.00

I am trying to merge duplicates with the Wage and only have one Code and Company Name as below

The result

CompCode Comp Name		Wage
S123	CompanyName_1	£3,000.00
S124	CompanyName_3	£3,000.00
S125	CompanyName_4	£3,000.00
S126	CompanyName_5	£3,000.00
S127	CompanyName_6	£3,000.00
S128	CompanyName_7	£3,000.00
S129	CompanyName_8	£3,000.00
S131	CompanyName_9	£3,000.00
S132	CompanyName_0	£3,000.00
S133	CompanyName_02	£3,000.00

Would you please advise the best way to do this, is it through Expression or the Script

Many thanks,

[sunny_talwar]

If you don't need two company for any other reasons, I would suggest handling this in the script to avoid carrying any unnecessary data in the application

[Anil_Babu_Samineni]

As sunny said, It will make sense to clean and neat in Data model. Still want PFA

[el_aprendiz111]

Hi,

COMP:
Mapping LOAD * Inline
[
CompCode,Comp Name
S123,CompanyName_1
S124,CompanyName_3
S125,CompanyName_4
S126,CompanyName_5
S127,CompanyName_6
S128,CompanyName_7
S129,CompanyName_8
S131,CompanyName_9
S132,CompanyName_0
S133,CompanyName_02
];


TMP:
LOAD *, ApplyMap('COMP',CompCode,Null()) AS COMP_NAME;
LOAD * Inline [
CompCode,Comp Name,Wage
S132,CompanyName_0,1000
S132,CompanyName_01,2000
S133,CompanyName_02,1000
S133,CompanyName_021,2000
S123,CompanyName_1,1000
S123,CompanyName_12,2000
S124,CompanyName_3,1000
S124,CompanyName_31,2000
S125,CompanyName_4,1000
S125,CompanyName_41,2000
S126,CompanyName_5,1000
S126,CompanyName_51,2000
S127,CompanyName_6,1000
S127,CompanyName_61,2000
S128,CompanyName_7,1000
S128,CompanyName_71,2000
S129,CompanyName_8,1000
S129,CompanyName_81,2000
S131,CompanyName_9,1000
S131,CompanyName_91,2000


];

[#5]

Hi,

You can also add a subfiled similar to

left( [Comp Name],13)

as CompanyName,

in the script.

[MarcoWedel · Accepted solution]

Hi,

one front end solution could be:

SET MoneyFormat='£#,##0.00;-£#,##0.00';

LOAD * FROM [https://community.qlik.com/thread/252411] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[#7]

Hi Anil,

Thank you very much for your help, this has worked with my report as well

[#8]

Hi Marco,

This was exactly what I was looking for and thank you very much for your help

[MarcoWedel]

You're welcome.

I'm glad it worked for you.

regards

Marco