Sum If FieldName contains (LOAD script)

[wallerjc]

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.

[sunny_talwar · Accepted solution]

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)

[wallerjc]

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

[sunny_talwar]

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

[Peter_Cammaert]

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

[wallerjc]

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.

[sasiparupudi1]

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?

[jonathandienst]

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

[rwunderlich]

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

[PuriVelasco]

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!