topic Re: Duplicate company name in QlikView

Duplicate company name

Tue, 07 Mar 2017 14:57:00 GMT

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,

Re: Duplicate company name

sunny_talwar — Tue, 07 Mar 2017 16:31:23 GMT

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

Re: Duplicate company name

Anil_Babu_Samineni — Tue, 07 Mar 2017 16:47:46 GMT

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

Re: Duplicate company name

el_aprendiz111 — Tue, 07 Mar 2017 16:55:38 GMT

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

];

Re: Duplicate company name

Tue, 07 Mar 2017 17:13:45 GMT

Hi,

You can also add a subfiled similar to

left( [Comp Name],13)

as CompanyName,

in the script.

Re: Duplicate company name

MarcoWedel — Tue, 07 Mar 2017 23:48:07 GMT

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

Re: Duplicate company name

Wed, 08 Mar 2017 10:02:39 GMT

Hi Anil,

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

Re: Duplicate company name

Wed, 08 Mar 2017 10:03:28 GMT

Hi Marco,

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

Re: Duplicate company name

MarcoWedel — Wed, 08 Mar 2017 11:31:11 GMT

You're welcome.

I'm glad it worked for you.

regards

Marco