Compare field value with string

[Pestaninha]

Hi, I have the table bellow and I need to count values of 'String'  that are <= 'Plan'. Function SubtringCount Count(String,Plan) doesn't work because will only count the Match

Is this possible in script?

Doc	Doc It	String	Plan	SubStringCount(String,Plan)	Expected result
7	16	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	7.00000000000000	4	2
8	17	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	7.00000000000000	4	2
1	10	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	8.00000000000000	6	8
2	11	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	8.00000000000000	6	8
3	12	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	8.00000000000000	6	8
4	13	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	8.00000000000000	6	8
5	14	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	8.00000000000000	6	8
6	15	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	8.00000000000000	6	8
9	18	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	9.00000000000000	3	11
10	19	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	9.00000000000000	3	11
11	20	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	9.00000000000000	3	11
16	25	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	10.00000000000000	2	13
17	26	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	10.00000000000000	2	13
20	29	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	12.00000000000000	1	14
19	28	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	14.00000000000000	1	15
14	23	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	15.00000000000000	2	17
15	24	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	15.00000000000000	2	17
18	27	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	16.00000000000000	1	18
12	21	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	17.00000000000000	2	20
13	22	10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9	17.00000000000000	2	20

[Pestaninha]

Many Thanks Kush, but the scripts stops before the execution of the final table and nothing happens

[Kushal_Chawda]

@Pestaninha  Not sure but if you can share your script I can help where is the issue. 

[Pestaninha]

Thanks, but I don't think is feasible, as i have  a table with nearly 9600 records and length of string can achieve 50000 , so i think it gets stuck because is a lot of information to compare . Really appreciate your help though

[Pestaninha]

Thanks Edwin, but still gives an error  'Out of memory' when running the script, I suppose is because is a lot of data to compare

[Kushal_Chawda]

@Pestaninha  length of the string doesn't matter .. there must be you are missing something. You can share screenshot of your script .

[Kushal_Chawda · Accepted solution]

@Pestaninha  Try below. I removed if condition from formula

let vFormula1 =null();
let vFormula2 =null();
let vString_Field_Name = 'String'; // define name of the field which holds String value
let vCompare_Field_with_String ='Plan'; // define field name which you need to compare with String

Data:
LOAD Doc, 
     [Doc It], 
     SubStringCount(String,'-')+1 as Count,
     String, 
     Plan
FROM
[https://community.qlik.com/t5/New-to-QlikView/Compare-field-value-with-string/td-p/1767283]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Max_Count:
LOAD max(Count) as Max_Count;
LOAD FieldValue('Count',RecNo()) as Count
AutoGenerate FieldValueCount('Count');

let vMax_Count = Peek('Max_Count',0,'Max_Count');

DROP Table Max_Count;

for i=1 to $(vMax_Count)

let vFormula1 = 'rangesum(';

let vFormula2 = '$(vFormula2)' & 'subfield($(vString_Field_Name),'&chr(39)&'-'&chr(39)&',$(i))<=$(vCompare_Field_with_String),';

NEXT 

let vFormula = '$(vFormula1)' & mid('$(vFormula2)',1,len('$(vFormula2)')-1) & ') *-1';

Final:
LOAD *,
     $(vFormula) as Results
Resident Data;

DROP Table Data;

let vFormula = null();
let vFormula1 = null();
let vFormula2 = null();

[Pestaninha]

Wow it worked. I've tried for a shorter period and it worked, less lines and it worked.

Fantastic! Really happy. Thanks again have a good day