Duplicate values in field.

[krishna20]

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 .

Like

      IS North America

       North America

       One IS Office

Thanks

krishna

[Gysbert_Wassenaar]

Perhaps like this:

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

[Anonymous]

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),'¤')

[krishna20]

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;

[Gysbert_Wassenaar]

A :

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

Units L

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

...........

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

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

[beck_bakytbek]

Hi Kalyan,

check this: Duplicates on multiple Excel Loads

i hope that resolves your issue

beck

[krishna20]

Hi Gysbert,

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

Please find the below script

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;

[Gysbert_Wassenaar]

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

[Anil_Babu_Samineni]

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

[wallerjc]

Hi

Can you try lookign at the PurgeChar example here?

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