Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

balrammahato204
Contributor

Set the unique values

Hi All,

Please help me out

i am not able to find the solution for my scenario

Please find the explanation

Input:-

Member ID

Country

1001

Ireland

1002

Ireland

1003

Ireland

1004

Netherland

1005

Netherland

1006

India


i have two input field Unique "Member ID" and Repeated "Country" values.

I want to get unique Countruy name for each member id.

for the unique name i want to add serial number in the last of the country name.

Means whenever the new country name will come then i want restart the sequence number and when a single country name will come then it will be as it is.

Please find the below table for better understanding.

Output Table:-

Member ID

Country

Unique Name

1001

Ireland

Ireland01

1002

Ireland

Ireland02

1003

Ireland

Ireland03

1004

Netherland

Netherland01

1005

Netherland

Netherland01

1006

India

India

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Set the unique values

There are several ways to do this, for example like this

INPUT:

LOAD [Member ID],

     Country,

     Autonumber([Member ID], Country) as CountryID

FROM

[https://community.qlik.com/thread/193298]

(html, codepage is 1252, embedded labels, table is @1);

LOAD *,

  If(CountryID = 1 and Previous(Country)<> Country, Country, Country & Num(CountryID,'00')) as [Unique Country]

Resident INPUT

ORDER BY Country, CountryID desc;

DROP TABLE INPUT;

DROP FIELD CountryID;

4 Replies
MVP
MVP

Re: Set the unique values

There are several ways to do this, for example like this

INPUT:

LOAD [Member ID],

     Country,

     Autonumber([Member ID], Country) as CountryID

FROM

[https://community.qlik.com/thread/193298]

(html, codepage is 1252, embedded labels, table is @1);

LOAD *,

  If(CountryID = 1 and Previous(Country)<> Country, Country, Country & Num(CountryID,'00')) as [Unique Country]

Resident INPUT

ORDER BY Country, CountryID desc;

DROP TABLE INPUT;

DROP FIELD CountryID;

Re: Set the unique values

Hi,

another somewhat similar solution could be:

table1:

LOAD *, Num(AutoNumber([Member ID],Country),'00') as number

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

Left Join

LOAD Country,

    Count(Distinct [Member ID]) as CountryNum

Resident table1

Group By Country;

Left Join

LOAD [Member ID],Country,

    Country&If(CountryNum>1,number) as [Unique Name]

Resident table1;

DROP Fields number, CountryNum;

hope this helps

regards

Marco

balrammahato204
Contributor

Re: Set the unique values

Thank you very much swuehi for your perfect solution

HirisH_V7
Honored Contributor

Re: Set the unique values

Hi,

Data:

LOAD * INLINE [

    MID, Country

    1, Ireland

    2, Ireland

    3, Ireland

    4, Netherland

    5, Netherland

    6, India

];

New:

Load

MID,

Country,

Country & MID as NewId

Resident Data;

Drop Table Data;

You can get the output like this ,

Newid.PNG

Hope this helps,

Regards,

Hirish

HirisH
“Aspire to Inspire before we Expire!”