script: new column based on range of string

[zeiherj]

Hi All,

im am trying to generate a new column based on a range of string values:

//something like if Alpha is in range 'A' to 'D' gen new column = 1, if not = 0
Load *,
if (Alpha = (stringRange, 'A', 'D'), 1, 0) as NewCol;

Characters:
Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

 How can I do this in Qlik sense?

Thanks in advance!

Johannes

[Kushal_Chawda]

@zeiherj  You already have number position for each characters in Num column so you can use that column to create a flag

Load *,
if (Num <=4, 1, 0) as NewCol;

Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

[zeiherj]

@Kushal_Chawda Thanks for the propt response!

Sorry, this was just sample data. Im am looking for a way to generate the new column based on the string column.

[Kushal_Chawda]

@zeiherj  What are the values in string column? Could you give some sample data examples?

[zeiherj]

ICD-Codes:

Load ICD inline 
[ICD
'A090'
'E713'
'J205'
'N100'
'N390'
'R310'
'T179'
'A020'
'A081'
'A099'
'A418'
'D550'
'D618'
'E713'
'G404'
'G932'
'H000'
'H601'
'J069'
'J101'
'J121'
'J210'
'J211'
'J218'
'J385'
'J390'];

Example for if-range statement: If ICD is in range from 'A000' to 'E999'

[Kushal_Chawda · Accepted solution]

@zeiherj  try below

map_characters:
Mapping Load Chr(RecNo()+Ord('A')-1) as Alpha, 
     RecNo() as Num 
autogenerate 26;

let vRangeStart = MapSubString('map_characters','A000');
let vRangeEnd = MapSubString('map_characters','E999');

ICD:
Load *,
     if(ICD_number >= $(vRangeStart) and ICD_number<= $(vRangeEnd),1,0) as flag;
Load *, 
     MapSubString('map_characters',ICD) as ICD_number 
inline [
ICD
'A090'
'E713'
'J205'
'N100'
'N390'
'R310'
'T179'
'A020'
'A081'
'A099'
'A418'
'D550'
'D618'
'E713'
'G404'
'G932'
'H000'
'H601'
'J069'
'J101'
'J121'
'J210'
'J211'
'J218'
'J385'
'J390'];

[zeiherj]

Thanks, @Kushal_Chawda. Works perfectly!