topic Re: Duplicate values in field. in QlikView

Duplicate values in field.

krishna20 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Folks,

I am having junk values in a filed like below. There is a special characters included and the value is repeating n times.

¤IS North America;¤IS North America;¤IS North America

¤North America;¤North America;¤North America

¤One IS Office;¤One IS Office;¤One IS Office

Please suggest me to restrict the junk values and to show only single value in the field .

IS North America

North America

One IS Office

Thanks

krishna

Re: Duplicate values in field.

Gysbert_Wassenaar — Tue, 18 Oct 2016 07:48:18 GMT

Perhaps like this:

subfield( KeepChar(Field_With_Junk, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 ;'), ';', 1)

Re: Duplicate values in field.

Anonymous — Tue, 18 Oct 2016 07:48:19 GMT

if all values belong to same scheme you may use (use fieldname instead of text)

=purgechar(SubField('¤IS North America;¤IS North America;¤IS North America',';',1),'¤')

Re: Duplicate values in field.

krishna20 — Tue, 18 Oct 2016 08:04:04 GMT

Hi Gysbert,

Thank you for your valuable reply. It's not working as you suggested.

My code is

A :

.............

Units L

.............

...........

.........................

left join (A)

LOAD ID,

concat(subfield( KeepChar([Units L], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 ;'), ';', 1),';') as [Units L1]

Resident A

Group By ID;

Re: Duplicate values in field.

Gysbert_Wassenaar — Tue, 18 Oct 2016 08:05:52 GMT

A :

.............

Units L

.............

...........

.........................

That doesn't look like a valid load statement to me. So I wouldn't expect that to work.

Re: Duplicate values in field.

beck_bakytbek — Tue, 18 Oct 2016 08:15:14 GMT

Hi Kalyan,

check this: Duplicates on multiple Excel Loads

i hope that resolves your issue

beck

Re: Duplicate values in field.

krishna20 — Tue, 18 Oct 2016 08:28:40 GMT

Hi Gysbert,

............... it means that there is some fields in the table.

Please find the below script

LOAD

[ YearMonth],

[Id ] as ID,

[ Units L1],

[ Units L1 Id],

[Units L2],

[Units L2 Id],

[ Units L3],

[ Units L3 Id],

[ Units L4],

[ Units L4 Id],

[ Units L5],

[ Units L5 Id]

FROM $(vQVD)\X.qvd

(qvd);

left join (A)

LOAD ID,

concat(subfield( KeepChar([Units L], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 ;'), ';', 1),';') as [Units L1]

Resident A

Group By ID;

Re: Duplicate values in field.

Gysbert_Wassenaar — Tue, 18 Oct 2016 08:32:23 GMT

Can you post that X.qvd file. Or if it's a very big file then a small representative subset of it?

Re: Duplicate values in field.

Anil_Babu_Samineni — Tue, 18 Oct 2016 08:45:54 GMT

KALYAN KRISHNA wrote:

A:

LOAD

    [ YearMonth],

        [Id ] as ID,

    [ Units L1],

[ Units L1 Id],

    [Units L2],

      [Units L2 Id],

    [ Units L3],

[ Units L3 Id],

    [ Units L4],

    [ Units L4 Id],

    [ Units L5],

[ Units L5 Id]

FROM $(vQVD)\X.qvd

(qvd);

left join (A)

LOAD ID,

concat(subfield( KeepChar([Units L], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 ;'), ';', 1),';') as [Units L1]

Resident A

Group By ID;

I am not seeing any more the [Units L] Field

concat(subfield( KeepChar([Units L], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 ;'), ';', 1),';') as [Units L1]

Does It you are facing Issue

Then Figure out where do you vi-sibling that Field

Re: Duplicate values in field.

wallerjc — Tue, 18 Oct 2016 15:25:18 GMT

Can you try lookign at the PurgeChar example here?

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/StringFunctions/PurgeChar.htm