Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Pestaninha
Contributor III
Contributor III
‎2020-12-09 07:06 AM

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

 

 

2,204 Views
0 Likes
1 Solution

Accepted Solutions
Kushal_Chawda
MVP
MVP
‎2020-12-10 07:00 PM

@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

1,869 Views
16 Replies
Taoufiq_Zarra
MVP
MVP
‎2020-12-09 07:44 AM

@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") 😉
1,287 Views
0 Likes
Taoufiq_Zarra
MVP
MVP
‎2020-12-09 07:44 AM

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") 😉
1,286 Views
0 Likes
Pestaninha
Contributor III
Contributor III
‎2020-12-09 09:26 AM
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

1,253 Views
0 Likes
Taoufiq_Zarra
MVP
MVP
‎2020-12-09 09:58 AM

@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") 😉
1,247 Views
0 Likes
Pestaninha
Contributor III
Contributor III
‎2020-12-09 10:03 AM
Author

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

Many thanks anyway

1,236 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2020-12-09 10:54 AM

@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

1,179 Views
0 Likes
edwin
Master II
Master II
‎2020-12-09 04:10 PM

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

 

 

Fields.qvw
1,162 Views
0 Likes
edwin
Master II
Master II
‎2020-12-09 04:46 PM

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

1,148 Views
0 Likes
Pestaninha
Contributor III
Contributor III
‎2020-12-10 08:04 AM
Author

In response to Kushal_Chawda

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

1,110 Views
0 Likes