Fix Values and Autogenerate in same field

[Shahzad_Ahsan]

Hi

I have City table which contain only "CityName".  I want to generate CityId with following condition:

I have more than 50 cities in the table. I want to fix "CityId" for some cities and rest should be autogenerated.

Please refer the below table, I want to fix values for cities A, B, C, D, E as 1, 2, 3, 4, 5 respectively. from CityName "F", it should autogenerate the values and should start from 6 and so on.

Is is possible to do with script??

CityName	CityId
A	1 (fixed)
B	2 (fixed)
C	3 (fixed)
D	4 (fixed)
E	5 (fixed)
F	6
..	7
..	..
..	..
..	..
Z

Thanks

[Anil_Babu_Samineni]

What do you mean when you say fixed for first 5 letters?

[Ksrinivasan]

hi,

yes, you can do,

1. Autogenerate(RowNo(), CityName) as City Id,

or

if(CityName='A' and 'B' and 'C' and 'D' and  'E','',Autogenerate(RowNo(), CityName) as City Id,

ksrinivasan

[Shahzad_Ahsan]

Hi Anil

Its not about first 5 letters. 

I have some cities which I need to fix values for those. 

For Ex: Value for Delhi should be 1 , Mumbai is 2  and Bangalore  is 3 and rest all cities will be 4,5,6 and so on.

[Shahzad_Ahsan]

Hi @Ksrinivasan 

Not working

[Ksrinivasan · Accepted solution]

hi,

try this

Input - yellow values you have to entered as you said.

LOAD
CityName,
CityId,
CityId & if(len(CityId)='0',(RowNo()),'') as New_City_ID

result

if you dont want middle column

set \\ in front of CityId

result:

ksrinivasan

[Shahzad_Ahsan]

@Ksrinivasan Thanks Buddy

Its working 👍