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

Scripting flaw?

Hello,

I am new to the forum and I have just completed a course on Qlikview (Developer and Designer I and II).

The following problem occurs when I am trying to write a script:

I have loaded the data from our SAP-system into some qvd-files.

From these qvd-files, I have loaded all the data from one table.

The data exists, among other things, from the following:

     [Gross external sales],
     [Gross internal sales],
     [Rebates external],
     Discount,     

I want to edit this a little bit to make it easier to handle, so I did the following:

     [Gross external sales] as GrossSales,
     [Gross internal sales] as ICSales,
     [Rebates external] as Rebates,
     Discount as Discounts,
     [Gross internal sales] + [Gross external sales] + [Rebates external] as NetSales,

The first 4 lines don't give any problems, they show the data they should at the front end table.

The fifth line however gives as outcome '0' on my front end, whilst line 1, 2 and 3 definitely contain data.

The problem seems to lie within the 'Gross internal sales' part, because when I remove that one, the outcome is indeed the 'Gross external sales' + the 'Rebates external'.

Does anyone have any idea what this could be?

Much appreciated!

Kind regards.

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Most likely, some of those three numbers have a null() value. Any arithmetical operation with null() will always return null() as a result. Try enclosing your fields in a RANGESUM function:

RANGESUM([Gross internal sales]),  [Gross external sales], [Rebates external]) as NetSales,

Notice that '+' needs to be replaced with commas. Rangesum() takes care of the nulls by padding them with zeros.

cheers,

Oleg

View solution in original post

8 Replies
Sokkorn
Master
Master

Hi,

Try this

[tblName]:
LOAD *,
GrossSales + ICSales + Rebates AS NetSales;
LOAD
[Gross external sales] as GrossSales,
[Gross internal sales] as ICSales,
[Rebates external] as Rebates,
Discount as Discounts
FROM TableName;

Regards,

Sokkorn

Anonymous
Not applicable
Author

Hello Sokkorn,

first, thanks for the help :-).

On topic: how should it look?

My script now looks like this:

LOAD
  // Keyfields
     %MaterialNumber_Key,
     %Customer_Key,
             
     // Sourcefields
     [Gross external sales] as GrossSales,
     [Gross internal sales] as ICSales,
     [Rebates external] as Rebates,
     Discount as Discounts,
     [Gross external sales] + [Gross internal sales] + [Rebates external] as NetSales,
     
     Year_Billing,
     Quarter_Billing,

     Month_Billing,
     Week_Billing,
     Day_Billing,

FROM
[CE11000_COPA LineItems_P60.qvd]
(qvd);


Now how should it look? Don't know where you want me to put the extra table?

Thanks in advance,

Stefan

Sokkorn
Master
Master

Hi Stefan,

Try below script

[TableName]:

LOAD *,

GrossSales + ICSales + Rebates AS NetSales;

LOAD   // Keyfields      %MaterialNumber_Key,      %Customer_Key,                    // Sourcefields      [Gross external sales] as GrossSales,      [Gross internal sales] as ICSales,      [Rebates external] as Rebates,      Discount as Discounts,    //  [Gross external sales] + [Gross internal sales] + [Rebates external] as NetSales,            Year_Billing,      Quarter_Billing,      Month_Billing,      Week_Billing,      Day_Billing, FROM [CE11000_COPA LineItems_P60.qvd] (qvd);

Let me know if this one help you.

Regards,

Sokkorn

Anonymous
Not applicable
Author

Hello Sokkorn,

sadly, this doesn't work :-(.

When I try the same for my Freight Costs or my Costs RAW, the same problem occurs:

[Freight costs internal] + [Freight costs external] as FreightCosts,
[Costs RAW internal] - [Costs RAW external] as Materials,

These also give a '0' on my front end.

When I name the internal and the external positions seperate in my script, like this:

[Freight costs internal]

[Freight costs external]

and add the together in my expression in my front end like this: "(sum([Freight costs internal])+sum([Freight costs external]))"

it does give the good value. This however I wanted to minimize, since that costs a lot of memory...

Any other ideas?

Kind regards,

Stefan

Not applicable
Author

hello,

it may come from the data itself. Is column [Gross internal sales] same format in sap as others ?

Try this :

num([Gross internal sales]) + [Gross external sales] + [Rebates external] as NetSales,

Regards

Gilles

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Most likely, some of those three numbers have a null() value. Any arithmetical operation with null() will always return null() as a result. Try enclosing your fields in a RANGESUM function:

RANGESUM([Gross internal sales]),  [Gross external sales], [Rebates external]) as NetSales,

Notice that '+' needs to be replaced with commas. Rangesum() takes care of the nulls by padding them with zeros.

cheers,

Oleg

Anonymous
Not applicable
Author

Hello Gilles and Oleg,

first I tried your approach Gilles. Sadly, it didn't work :-(.

Then I tried your approach Oleg... And it worked 🙂

Thank you all for helping me out, and Oleg, thanks for finding the correct answer to the problem!

Kind regards,

Stefan

suniljain
Master
Master

Following expression will also work

Num(Trim([Gross internal sales])))+ Num(Trim( [Gross external sales]))+ Num(Trim([Rebates external]))) as NetSales,