Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

UserID2687
Contributor

Set an identity for data dynamically Qlik sense Script

Hi,

I need to set an Identity for the values.

Example

Below table is my input data

CountryProductTotal Sale
USAA32
UKA4564
JapanAD89
ChinaJU342
GermanyA1234
USAB677
UKTG32
JapanB4564
ChinaB32
GermanyFG4564
USAC89
UKHG342
JapanC1234
ChinaC677
GermanyTG32
USAD4564
UKD89
JapanAD342
ChinaD1234
GermanyD677

 

i need to an ID Based on country like USA Means 1, UK means 2...

Required o/p,

Country_IdCountryProductTotal Sale
1USAA32
2UKA4564
3JapanAD89
4ChinaJU342
5GermanyA1234
1USAB677
2UKTG32
3JapanB4564
4ChinaB32
5GermanyFG4564
1USAC89
2UKHG342
3JapanC1234
4ChinaC677
5GermanyTG32
1USAD4564
2UKD89
3JapanAD342
4ChinaD1234
5GermanyD677

 

Help me to achieve this by Qlik scripting.

Thanks in advance!!!

1 Solution

Accepted Solutions

Re: Set an identity for data dynamically Qlik sense Script

You can try using AutoNumber in the script

AutoNumber(Country) as Country_id

If this hinders reload time, you can use Peek/previous to do this in the script.

Table:
LOAD * INLINE [
    Country, Product, Total Sale
    USA, A, 32
    UK, A, 4564
    Japan, AD, 89
    China, JU, 342
    Germany, A, 1234
    USA, B, 677
    UK, TG, 32
    Japan, B, 4564
    China, B, 32
    Germany, FG, 4564
    USA, C, 89
    UK, HG, 342
    Japan, C, 1234
    China, C, 677
    Germany, TG, 32
    USA, D, 4564
    UK, D, 89
    Japan, AD, 342
    China, D, 1234
    Germany, D, 677
];

FinalTable:
LOAD *,
	 If(Country = Previous(Country), Peek('Country_Id'), RangeSum(Peek('Country_Id'), 1)) as Country_Id
Resident Table
Order By Country;

DROP Table Table;
2 Replies
Partner
Partner

Re: Set an identity for data dynamically Qlik sense Script

Try

autonumber(Country) as Country_Id

Re: Set an identity for data dynamically Qlik sense Script

You can try using AutoNumber in the script

AutoNumber(Country) as Country_id

If this hinders reload time, you can use Peek/previous to do this in the script.

Table:
LOAD * INLINE [
    Country, Product, Total Sale
    USA, A, 32
    UK, A, 4564
    Japan, AD, 89
    China, JU, 342
    Germany, A, 1234
    USA, B, 677
    UK, TG, 32
    Japan, B, 4564
    China, B, 32
    Germany, FG, 4564
    USA, C, 89
    UK, HG, 342
    Japan, C, 1234
    China, C, 677
    Germany, TG, 32
    USA, D, 4564
    UK, D, 89
    Japan, AD, 342
    China, D, 1234
    Germany, D, 677
];

FinalTable:
LOAD *,
	 If(Country = Previous(Country), Peek('Country_Id'), RangeSum(Peek('Country_Id'), 1)) as Country_Id
Resident Table
Order By Country;

DROP Table Table;