Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum If on Load Script

Hi all,

I am a bit stuck at the moment.

I have a field called "LOG" that has over 1 million lines of text. I am trying to count the number of times that a specific text appears amongst all those lines.

At the moment I am using an expression on my chart and it work perfectly using:   sum(if(substringcount(LOG,'Text'),1,0))

However, I would like to do that sum on the load script so I can delete the field LOG while saving all the other results on a qvd file.

I have tried to use that expression on the script and save it as any other new field but it returns invalid expression.

Any ideas on how can I do the sum if on the script?

Thank you!

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

Whenever you are using an aggregation function in the Load script you need to use the Group By clause

example

Load

F1,

F2,

F3,

Sum(Measure) as Amounttotal

From xxxxxxx

Where xxxxxx

Group by F1,F2,F3;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

3 Replies
vinieme12
Champion III
Champion III

Whenever you are using an aggregation function in the Load script you need to use the Group By clause

example

Load

F1,

F2,

F3,

Sum(Measure) as Amounttotal

From xxxxxxx

Where xxxxxx

Group by F1,F2,F3;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
el_aprendiz111
Specialist
Specialist

Hi,

1 example:

LOAD *, If(peek(CHR)=CHR, peek(COUNT)+1, 1) as COUNT;
LOAD * Inline
[
CHR
K
K
K
K
B
L
L
L
L
L
D
D
D
D
D
G
]
;
EXIT Script;

Anonymous
Not applicable
Author

Thank you very much to both of your for taking the time to reply to my question.

It is always good to see different ways of doing it.

I am currently using the Group By option from a Resident temporal table and it allows my to do the calculations I needed and store the final table as qvd without the million lines of code reducing the size of the logs massively.

Once again, thank you both for your time and help.