topic script: new column based on range of string in App Development

script: new column based on range of string

zeiherj — Wed, 11 Sep 2024 08:41:12 GMT

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

Re: script: new column based on range of string

Kushal_Chawda — Wed, 11 Sep 2024 09:18:47 GMT

@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;

Re: script: new column based on range of string

zeiherj — Wed, 11 Sep 2024 09:23:00 GMT

@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.

Re: script: new column based on range of string

Kushal_Chawda — Wed, 11 Sep 2024 09:27:07 GMT

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

Re: script: new column based on range of string

zeiherj — Wed, 11 Sep 2024 09:38:10 GMT

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'

Re: script: new column based on range of string

Kushal_Chawda — Wed, 11 Sep 2024 10:42:52 GMT

@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'];

Re: script: new column based on range of string

zeiherj — Wed, 11 Sep 2024 11:45:17 GMT

Thanks, @Kushal_Chawda. Works perfectly!