topic Re: Sum If on Load Script in QlikView

Sum If on Load Script

Anonymous — Sat, 26 Aug 2017 10:07:36 GMT

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!

Re: Sum If on Load Script

vinieme12 — Sat, 26 Aug 2017 10:31:24 GMT

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;

Re: Sum If on Load Script

el_aprendiz111 — Sat, 26 Aug 2017 17:27:37 GMT

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;

Re: Sum If on Load Script

Anonymous — Sun, 27 Aug 2017 09:04:03 GMT

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.