Management of empty values

Laurent Bondon on May 24, 2011

In one of my mashdata, the XL file read is a parameter. I don't think that's very important to understand, but just in case... The columns are the same in all these excel files.

Nevertheless, I think it should be the same behaviour with only one XL file in which, sometime there are some empty values in a date column.

Depending on missing value, the date column is considered as text or date.

My objective is to add a column to determine if we are late and if yes, how many days. (it would be enough to know if we are late or not I think).

Any idea ?

I found how to calculate a difference of days between too dates (by changing the type and using an arythmetic component). But I am blocked with the treatment of the empty values :

- if I know that the type of the date will be text, I have a workaround but then, the replace function Iuse does not work when the column will be considered as a date.

- if I know that the type will be date it works but when it is consider as text, it does not.

What is the best solution to manage the empty fields ?

Laurent

Notify Moderator

7 Replies

TS

Thomas Staub on May 25, 2011

Hello Laurent

I have no idea why empty cells in a date-column can lead to different column-types while execution. That shold only happens while definition time (in Feededitor)!

But i think you have possibilities to handle the problem.

First you can define this column as textcolumn! In the Excel-Source-Operator you can push the "Configure column" button. In this dialog you can select the checkbox "Type as text"!

Then you can change the columntype to date (change columntype-operator). At this point the columntype shold allways be DATE!

Before you calculate the time-difference you shold remove the empty cells. That you can do with the filter-operator ("let values pass" if "is not empty"). If you need all rows you can split the dataflow (Data feeds->copy) before the filteroperator and join (Data feeds->combine) the two flows after the calculation of the difference.

I hope whith this proposals you can reach your objective.

Thomas

Like

Sign in
or register to reply.

Notify Moderator
Laurent Bondon Author on May 25, 2011

Thank you Thomas for your answer. Maybe the type of the column comes from the fact that the XL file to read is a parameter ?

Anyway, I think having the "Type as text" option is not good because the excel file will be used for other purposes and I don't want to modify them all.

The copy of the data feeds is an interresting alternative. A bit PITA but, if no better solution is proposed, I'll give a try.

Thank you for your help !

Laurent

Like

Sign in
or register to reply.

Notify Moderator
Stephan Freudl on May 25, 2011

Hi,

actually I don't really understand the problem based on the info you provide. But I assume it is about calculating the difference between two date columns where some rows contain an empty cell.

You might use the replace value operator to find a useful default value. In case this is not possible you may filter out these rows by using the filter operator.

Regards

Stephan

Like

Sign in
or register to reply.

Notify Moderator
Laurent Bondon Author on May 25, 2011

Hi Stephan,

Sorry for not being clear.

This is the bahaviour when I have a file with only dates in its column.

This is the file when I have missing values :

The idea is to have one datafeed with a column containing the difference in days between now and the date in the column or 0 if the value is missing.

Maybe the product is not yet ready for it and as suggested by Thomas I can treat by copying the data and then treating the date where present in one side and where not present in the other side.

But if I need to do this for 4 date column, it will start to be a mess...

Laurent

Like

Sign in
or register to reply.

Notify Moderator
Laurent Bondon Author on May 25, 2011

So I tryed the proposal from Thomas :

When I change the file used as a parameter, it reinitaite the "filter values" block" and remove the "is not empty" and "is empty" selection.

So, the mashapp is again in error...

Damned, no solution so far :-(

Like

Sign in
or register to reply.

Notify Moderator
Stephan Freudl on May 30, 2011

Hi Laurent,

I think I was able to reproduce the problem. One of your user input fields privides the name for different Excel spreadsheets. Although they have the same columns one of them does not have any values in a date column.

So whenever you change the debug value within the feed editor MashZone tries to gues the column configuration again. For one debug value this results in a date column the next time - for another column - it results in a text colum. Therefore subsequent operators, e.g. the filter value operator, behave different or fall back to their default configuration.

Good news: This does not happen at runtime. So the message could be avoided based on the debug value used last.

So I may ask you to point your data source to an Excel having dates and no null values. Let MashZone guess the columns. And adjust the filter operators (to test for empty cells). And save the datafeed.

To verify you may create an empty MashApp which hosts two widgets: A) a combo box which enables you to select which spreadsheet to read from. B) a table which displays the feed result. The error should not occur.

Please contact me directly in case it doesn't solve your problem.

Regards

Stephan

Like

Sign in
or register to reply.

Notify Moderator
Laurent Bondon Author on May 30, 2011

Thank you Stephan,

It solved the problem apparently! Yessssss!

The choice of the debug field is then capital!

Laurent

Like

Sign in
or register to reply.

Notify Moderator

Management of empty values

7 Replies

ARIS User Group DACH meeting

ARIS Summit 2025 | Kuala Lumpur

ARIS Super User Group Meeting