A fuzzy search in the script - Qlik Community

josephinetedesc · ‎2015-05-24

Hi

I am between old codes and new codes and I need to code the following:

so there are about 100 activities, I need to code the 1 to 22 and the 000s to be a certain type of activity, for example ActivityType1 and the rest of the activities to be ActivityType 2.

This did not work:

if(IsNum(Right(Activity,2)) <23, 'ActivityType1', 'ActivityType2') as ActivityType,

should I perhaps make eActivity a number - with

if(num(eActivity) <=22, , 'ActivityType1', 'ActivityType2') as ActivityType, (no that does not work ... I was hoping 000-1 would read as a number)

eActivity
1
2
3
4
5
9
10
11
12
15
16
18
20
21
22
000-12
000-13
000-14
000-15
000-18
000-19
000-21
000-22
000-25
000-27
000-28
01-abc
02-bcd

Thank you

Jo

marcus_sommer · ‎2015-05-24

This would work if you nested a few more if-loops and extend the checks for splitting, converting and formatting the values. I mean things like: if(num(num#(subfield(value, '-', 1))) = 'XYZ' .... But I suggest to use Mapping to solve this, see: Mapping … and not the geographical kind

- Marcus

maxgro · ‎2015-05-24

LOAD

eActivity,

if(IsNum(eActivity) and eActivity<=22 or Left(eActivity,3)='000', '1', '2') as Flag

;

LOAD eActivity

FROM

[https://community.qlik.com/thread/165318]

(html, codepage is 1252, embedded labels, table is @1);

MarcoWedel · ‎2015-05-24

Hi,

a more generic solution using analysis of field value patterns could be:

tabActivities:
LOAD *,
    If(Match(eActivityFormat,'0','0-0'),'ActivityType1','ActivityType2') as ActivityType,
    'ActivityType'&eActivityFormatID as ActivityType2;
LOAD *,
    AutoNumber(eActivitySubFormat,'eActivitySubFormat') as eActivitySubFormatID,
    AutoNumber(eActivityFormat,'eActivityFormat') as eActivityFormatID;
LOAD *,
    Text(Replace(PurgeChar(Capitalize(Replace(PurgeChar(Capitalize(Lower(eActivitySubFormat)),'a'),'0','b')),'b'),'B','0')) as eActivityFormat;
LOAD eActivity,
    Text(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Upper(eActivity),'1','0'),'2','0'),'3','0'),'4','0'),'5','0'),'6','0'),'7','0'),'8','0'),'9','0'),
    'B','A'),'C','A'),'D','A'),'E','A'),'F','A'),'G','A'),'H','A'),'I','A'),'J','A'),'K','A'),'L','A'),'M','A'),'N','A'),
    'O','A'),'P','A'),'Q','A'),'R','A'),'S','A'),'T','A'),'U','A'),'V','A'),'W','A'),'X','A'),'Y','A'),'Z','A')) as eActivitySubFormat
FROM [https://community.qlik.com/thread/165318] (html, codepage is 1252, embedded labels, table is @1);