Set the unique values

[balrammahato204]

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

[swuehl · Accepted solution]

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;

[MarcoWedel]

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]

Thank you very much swuehi for your perfect solution

[HirisH_V7]

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 ,

Hope this helps,

Regards,

Hirish