Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Shahzad_Ahsan
Creator III
Creator III

Fix Values and Autogenerate in same field

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??

CityNameCityId
A1 (fixed)
B2 (fixed)
C3 (fixed)
D4 (fixed)
E5 (fixed)
F
..7
....
....
....
Z 

 

Thanks

Labels (1)
1 Solution

Accepted Solutions
Ksrinivasan
Specialist
Specialist

hi,

try this

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

Ksrinivasan_1-1612171914834.png

 

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

result

Ksrinivasan_0-1612171875866.png

if you dont want middle column

set \\ in front of CityId

Ksrinivasan_3-1612172033910.png

 

result:

Ksrinivasan_2-1612172004008.png

ksrinivasan

View solution in original post

6 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Ksrinivasan
Specialist
Specialist

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Hi @Ksrinivasan 

Not working

Ksrinivasan
Specialist
Specialist

hi,

try this

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

Ksrinivasan_1-1612171914834.png

 

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

result

Ksrinivasan_0-1612171875866.png

if you dont want middle column

set \\ in front of CityId

Ksrinivasan_3-1612172033910.png

 

result:

Ksrinivasan_2-1612172004008.png

ksrinivasan

Shahzad_Ahsan
Creator III
Creator III
Author

@Ksrinivasan Thanks Buddy

Its working 👍