topic Re: Set the unique values in QlikView

Set the unique values

balrammahato204 — Fri, 27 Nov 2015 18:02:22 GMT

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

Re: Set the unique values

swuehl — Fri, 27 Nov 2015 18:20:31 GMT

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

MarcoWedel — Sat, 28 Nov 2015 01:28:16 GMT

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

Re: Set the unique values

balrammahato204 — Sat, 28 Nov 2015 07:09:09 GMT

Thank you very much swuehi for your perfect solution

Re: Set the unique values

HirisH_V7 — Sat, 28 Nov 2015 07:30:08 GMT

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