Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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;
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;