Handling Null values while extracting data from SAP

[Rabia_Taimur]

I am extracting employee experience data from SAP. I want total experience of the employee within our company and before joining our company.

There are many cases where this is employees 1st job and we do not have any data saved for him in previous employment data source. In the screenshot below, as Pre company experience is not available, i cannot calculate total experience as well

I have used below script to define Null values for the dates being used to compute Pre Company Experience but it still doesnt work

NullAsValue BEGDA,ENDDA;

Set NullValue = '0';

[Golem]

---

but it still doesnt work

---

That gives no information at all. What does not work? Was happens if you do that?

Did you add 

SET NullDisplay=0;

as a new line in the "Main" section in the Data Editor?

[steeefan]

Try setting the actual value of these fields to 0:

LOAD
  Alt(BEGDA, 0) AS BEGDA,
  Alt(ENDDA, 0) AS ENDDA,
  //..
FROM
  [Table.qvd]
  (qvd);

This will set the value of BEGDA and ENDDAT to zero if they are NULL.

Referece: alt - script and chart function

[Rabia_Taimur]

Thank you both for the quick response, but the solutions arent working 😞

@Golem i need to get rid of these dashes(-) in pre company experience so that i can calculate employee's total experience, else i get '-' in total experience as well

[steeefan]

The dash is is simply a representation of a cell that contains no value, i.e. is NULL. If you want to use that for calculations, it's best to assign each empty cell zero as an actual value.

Please share the relevant code from your LOAD script. It will be much easier to help you then.

[Rabia_Taimur]

Below is the code i am using:

NullAsValue BEGDA,ENDDA;

Set NullValue = 0;
Set NullDisplay=0;

//Pre Experience
PA9028:
LOAD
Right(PERNR,4) As Emp_No,
alt(BEGDA,0) as Joining_Dt,
alt(ENDDA,0) as Leaving_Dt,
ZEMPLOYER as Employer,
ZPOSITION as Position_,
round((Alt(ENDDA,0)-Alt(BEGDA,0))/365,.1) as Duration
FROM PA9028_LOAD.qvd (qvd);

[Rabia_Taimur]

Used Alt() function for displaying Duration in the chart and still getting the '-'

[steeefan · Accepted solution]

Try this:

//NullAsValue BEGDA,ENDDA;
//Set NullValue = 0;
//Set NullDisplay=0;

PA9028:
LOAD
  *,
  Round((Leaving_Dt - Joining_Dt) / 365, .1) AS Duration;
LOAD
  Right(PERNR, 4) AS Emp_No,
  Alt(BEGDA, 0) AS Joining_Dt,
  Alt(ENDDA, 0) AS Leaving_Dt,
  ZEMPLOYER AS Employer,
  ZPOSITION AS Position_
FROM
  [lib://PA9028_LOAD.qvd]
  (qvd);

[Rabia_Taimur]

Thank you for the help, much appreciated!!