topic Re: Sum If FieldName contains (LOAD script) in QlikView

Sum If FieldName contains (LOAD script)

wallerjc — Thu, 27 Aug 2015 13:07:12 GMT

Hi,

Sorry for not being able to show any of my workings, but I'm really stuck on where to begin here.

I would like to write a load script similar to this

LOAD

Test(Conversion1),

Test(Conversion2),

If(Field Name) Contains "Test" then Sum(FieldName) as Sales

FROM

BLah

Does this make sense? I want to pick out fields that contain "Test" and sum their contents.

Thanks,

James.

Re: Sum If FieldName contains (LOAD script)

sunny_talwar — Thu, 27 Aug 2015 13:10:11 GMT

May be this:

LOAD

Test(Conversion1),

Test(Conversion2),

If(WildMatch(Field Name, '*Test*', Sum(FieldName)) as SumFieldName

FROM BLah

Group By Test(Conversion1), Test(Conversion2)

Re: Sum If FieldName contains (LOAD script)

wallerjc — Thu, 27 Aug 2015 13:14:33 GMT

This would work in theory I guess but "FieldName" needs to be a variable, I don't actually have a FieldName called FieldName. It would need to look through all fields in the table

Re: Sum If FieldName contains (LOAD script)

sunny_talwar — Thu, 27 Aug 2015 13:16:33 GMT

Hmm!!! That's a curve ball. Would you be able to share some sample data and your expected output?

Re: Sum If FieldName contains (LOAD script)

Peter_Cammaert — Thu, 27 Aug 2015 13:20:33 GMT

A LOAD reads records one-by-one, so Summing fields doesn't make sense unless you group records according to predefined rules (for example by customer, product, year or whatever you think is necessary)

In a LOAD specification, column names must be explicitly specified (litterally) or listed as a whole by way of an *. You want to make the expression dependent on the field at hand, but you don't specify any fields.

Moreover, an imaginary construct like

IF (MyFirstFieldTest has "Test" in its name, Do something) AS SumOfMyFirstFieldTest,

is an overly complex way of saying just

Do something with MyFirstFieldTest AS SumOfMyFieldTest,

because you can already see that there is "Test" in its name.

What you're after can be done however using a field list, some procedural code (i.e. a LOOP) and $-sign substitution. But in that case almost everything is happening outside of the LOAD statement itself. A LOAD statement can specify conditionally executed code, but cannot have a conditional syntax.

Peter

Re: Sum If FieldName contains (LOAD script)

wallerjc — Thu, 27 Aug 2015 13:24:41 GMT

Hi Mate

check out the attachment here, You can see I would like to sum all the columns which say "Sales" in their field

BUT here's the real kicker, if I added another field called Sales(Ten) I would like that to also be summed.

Is this possible to your knowledge? It's certainly too difficult for me.

Re: Sum If FieldName contains (LOAD script)

sasiparupudi1 — Thu, 27 Aug 2015 13:36:14 GMT

you can not have aggregation without group by clause.. I think you need to re look at on how to achieve your requirement.. Could you may be provide a sample?

Re: Sum If FieldName contains (LOAD script)

jonathandienst — Thu, 27 Aug 2015 13:48:25 GMT

It sounds to me like you have a poor model structure and which requires you to force fit a complex solution.

It is almost always better to transform your data model into a form that simplifies further processing and the subsequent expressions in the front end. I think you should explore making use of a CrossTable load to convert these field names into values that can be used by conventional expressions.

Also look at these sources:

Get started with developing qlik datamodels

Advanced topics for creating a qlik datamodel

Re: Sum If FieldName contains (LOAD script)

rwunderlich — Thu, 27 Aug 2015 17:59:58 GMT

You can do it in two passes like this:

Data:

LOAD *

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

SET vLoad=;

FOR i = 1 to NoOfFields('Data')

LET vFname = FieldName($(i),Data);

IF '$(vFname)' LIKE 'sales*' THEN

LET vLoad = '$(vLoad)' & chr(10) & ',sum([$(vFname)]) as [Summed_$(vFname)]';

ENDIF

NEXT i

Summed:

LOAD

Name

$(vLoad)

RESIDENT Data

GROUP BY Name;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Sum If FieldName contains (LOAD script)

PuriVelasco — Mon, 07 Dec 2020 12:58:49 GMT

Hello,

do you know how it would be if we had two fields instead of one field? Some like this:

if(WildMatch([EAN],'xxxxxx') or WildMatch([EAN],'yyyyyy'),sum(stockLineal), stockLineal) AS Control_stock_stockLineal,

I need to sum the stockLineal field when the EAN field is 'xxxxx' or 'yyyyy'.

Thank you in advance!