topic Re: Compare field value with string in QlikView

Compare field value with string

Pestaninha — Wed, 09 Dec 2020 12:06:26 GMT

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

Re: Compare field value with string

Taoufiq_Zarra — Wed, 09 Dec 2020 12:44:24 GMT

@Pestaninha Maye be like :

Data: load *,if(Subfield(String,'-')<=Num(Plan),1,0) as SubTmp; ; LOAD * INLINE [ Doc, Doc It, String, Plan 7, 16, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 7.00000000000000 8, 17, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 7.00000000000000 1, 10, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 2, 11, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 3, 12, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 4, 13, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 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, 15, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 9, 18, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 10, 19, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 11, 20, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 16, 25, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 10.00000000000000 17, 26, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 10.00000000000000 20, 29, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 12.00000000000000 19, 28, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 14.00000000000000 14, 23, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 15.00000000000000 15, 24, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 15.00000000000000 18, 27, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 16.00000000000000 12, 21, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 17.00000000000000 13, 22, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 17.00000000000000 ]; output: noconcatenate load Doc, [Doc It], String, Plan resident Data; left join load Doc, [Doc It],String, Sum(SubTmp) as [Expected result] resident Data group by Doc, [Doc It], String; drop table Data;

output:

Re: Compare field value with string

Taoufiq_Zarra — Wed, 09 Dec 2020 12:44:55 GMT

Re: Compare field value with string

Pestaninha — Wed, 09 Dec 2020 14:26:06 GMT

Thanks Taoufiq , the problem with this is that generates a record for every comparison that does against the sub field generating hundreds of thousands of lines. Not feasible when you have big tables

Re: Compare field value with string

Taoufiq_Zarra — Wed, 09 Dec 2020 14:58:34 GMT

@Pestaninha and by adding distinct like :

Data: load *,if(Subfield(String,'-')<=Num(Plan),1,0) as SubTmp; ; LOAD * INLINE [ Doc, Doc It, String, Plan 7, 16, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 7.00000000000000 8, 17, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 7.00000000000000 1, 10, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 2, 11, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 3, 12, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 4, 13, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 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, 15, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 9, 18, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 10, 19, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 11, 20, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 16, 25, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 10.00000000000000 17, 26, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 10.00000000000000 20, 29, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 12.00000000000000 19, 28, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 14.00000000000000 14, 23, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 15.00000000000000 15, 24, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 15.00000000000000 18, 27, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 16.00000000000000 12, 21, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 17.00000000000000 13, 22, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 17.00000000000000 ]; output: noconcatenate load distinct Doc, [Doc It], String, Plan resident Data; left join load Doc, [Doc It],String, Sum(SubTmp) as [Expected result] resident Data group by Doc, [Doc It], String; drop table Data;

output :

Re: Compare field value with string

Pestaninha — Wed, 09 Dec 2020 15:03:55 GMT

I'm afraid not , for this example the first load generates 400 lines out of a 20 line table.

Many thanks anyway

Re: Compare field value with string

Kushal_Chawda — Wed, 09 Dec 2020 15:57:02 GMT

@Pestaninha try below approach without increasing your input data

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 Table; 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)' & 'if(subfield($(vString_Field_Name),'&chr(39)&'-'&chr(39)&',$(i))<=$(vCompare_Field_with_String),1,0),'; NEXT let vFormula = '$(vFormula1)' & mid('$(vFormula2)',1,len('$(vFormula2)')-1) & ')'; Final: LOAD *, $(vFormula) as Results Resident Data; DROP Table Data; let vFormula = null(); let vFormula1 = null(); let vFormula2 = null();

Re: Compare field value with string

edwin — Wed, 09 Dec 2020 21:10:49 GMT

how about this:

load Doc, [Doc It], String, Plan, count(Field) where Field <=Plan group by Doc, [Doc It], String, Plan; load *, subfield( String,'-', iterno()) as Field, Iterno() as Index while iterno()<=20; LOAD * INLINE [ Doc, Doc It, String, Plan 7, 16, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 7.00000000000000 8, 17, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 7.00000000000000 1, 10, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 2, 11, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 3, 12, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 4, 13, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 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, 15, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 8.00000000000000 9, 18, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 10, 19, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 11, 20, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 9.00000000000000 16, 25, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 10.00000000000000 17, 26, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 10.00000000000000 20, 29, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 12.00000000000000 19, 28, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 14.00000000000000 14, 23, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 15.00000000000000 15, 24, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 15.00000000000000 18, 27, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 16.00000000000000 12, 21, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 17.00000000000000 13, 22, 10-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-9, 17.00000000000000 ];

Re: Compare field value with string

edwin — Wed, 09 Dec 2020 21:46:35 GMT

and since you are grouping by the original fields there is no increase in row count

Re: Compare field value with string

Pestaninha — Thu, 10 Dec 2020 13:04:12 GMT

Many Thanks Kush but I can't make this work it does not execute the Final table. Script simply stops before that

Re: Compare field value with string

Pestaninha — Thu, 10 Dec 2020 15:28:17 GMT

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

Re: Compare field value with string

Kushal_Chawda — Thu, 10 Dec 2020 15:39:52 GMT

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

Re: Compare field value with string

Pestaninha — Thu, 10 Dec 2020 19:48:37 GMT

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

Re: Compare field value with string

Pestaninha — Thu, 10 Dec 2020 19:50:13 GMT

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

Re: Compare field value with string

Kushal_Chawda — Thu, 10 Dec 2020 22:26:49 GMT

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

Re: Compare field value with string

Kushal_Chawda — Fri, 11 Dec 2020 00:00:50 GMT

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

Re: Compare field value with string

Pestaninha — Fri, 11 Dec 2020 09:30:33 GMT

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