Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
UserID2626
Creator III
Creator III

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

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

 

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

Required o/p,

Country_Id Country Product Total Sale
1 USA A 32
2 UK A 4564
3 Japan AD 89
4 China JU 342
5 Germany A 1234
1 USA B 677
2 UK TG 32
3 Japan B 4564
4 China B 32
5 Germany FG 4564
1 USA C 89
2 UK HG 342
3 Japan C 1234
4 China C 677
5 Germany TG 32
1 USA D 4564
2 UK D 89
3 Japan AD 342
4 China D 1234
5 Germany D 677

 

Help me to achieve this by Qlik scripting.

Thanks in advance!!!

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

Try

autonumber(Country) as Country_Id
sunny_talwar

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;