Scripting flaw?

[Anonymous]

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.

[Sokkorn]

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]

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]

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]

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

[#6]

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 · Accepted solution]

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]

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]

Following expression will also work

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