Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Pestaninha
Contributor II
Contributor II

Compare field value with string

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?

DocDoc ItStringPlanSubStringCount(String,Plan)Expected result
71610-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-97.0000000000000042
81710-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-97.0000000000000042
11010-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-98.0000000000000068
21110-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-98.0000000000000068
31210-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-98.0000000000000068
41310-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-98.0000000000000068
51410-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-98.0000000000000068
61510-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-98.0000000000000068
91810-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-99.00000000000000311
101910-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-99.00000000000000311
112010-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-99.00000000000000311
162510-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-910.00000000000000213
172610-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-910.00000000000000213
202910-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-912.00000000000000114
192810-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-914.00000000000000115
142310-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-915.00000000000000217
152410-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-915.00000000000000217
182710-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-916.00000000000000118
122110-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-917.00000000000000220
132210-10-12-14-15-15-16-17-17-7-7-8-8-8-8-8-8-9-9-917.00000000000000220

 

 

1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

16 Replies
Taoufiq_Zarra

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

Taoufiq_Zarra_0-1607517856167.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Taoufiq_Zarra

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Pestaninha
Contributor II
Contributor II
Author

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

Taoufiq_Zarra

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

Taoufiq_Zarra_0-1607525905338.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Pestaninha
Contributor II
Contributor II
Author

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

Many thanks anyway

Kushal_Chawda

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

 

 

Kush_0-1607529281496.png

edwin
Specialist III
Specialist III

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

 

 

edwin
Specialist III
Specialist III

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

Pestaninha
Contributor II
Contributor II
Author

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