Problem empty fields Excel

[#1]

Hi,

I have a small Problem. My Data in Excel has 3 Colums: Company Holding, Companies' subsidiary, Amount.

Example

Company Holding : Apple, Companies subsidiary: Apple subsidiary 1, Apple subsidiary 2.

Some of the Company Holdings don't have subsidiaries, so there Amount in Excel is "-".

Company Holding: Microsoft , Companies subsidiary : -

Problem: When I take a selection direct In box 2 with the subsidiarys, the "-" is not displayed and so the total amount is not considering the amount of Microsoft. Does anybody have an idea how to solve this problem?

[maxgro]

A solution could be to replace '-' or empty or null with somwthing the user can understand

UNKNOW, NOT AVAIL, ....

if( [yourcompanysubfield] = '-', 'UNKNOWN', [yourcompanysubfield]) as [yourcompanysubfield]

or

if( len(trim([yourcompanysubfield])) = 0, 'UNKNOWN', [yourcompanysubfield]) as [yourcompanysubfield]

[#3]

Thanks @maxgro for your help.

I tried both strings in the loading script:

LOAD
if( [yourcompanysubfield] = '-', 'UNKNOWN', [yourcompanysubfield]) as [yourcompanysubfield],
if( len(trim([yourcompanysubfield])) = 0, 'UNKNOWN', [yourcompanysubfield]) as [yourcompanysubfield]

but both don't work. Does someone have another idea?

May the problem be, that the data source is a *.qvw file?

Binary

[Anonymous]

Try at script end, like:

load

Company Holding,

if([Companies' subsidiary]='-' or [Companies' subsidiary]=' ','NoSubsidiary', [Companies' subsidiary]) as [Companies' subsidiary]

Amount

From YourTable;

Now at UI level if you select any company where their is no subsidiary, you will get NoSubsidiary

[maxgro]

post  your excel, it would be simpler to help you

[#6]

Thanks for your support. The dataset is from my company, so I can't post it here.

The Problem is, that I get my Data by binary load from an other QVW.

So your provided solutions don't work.

Beginning of the script :

Binary

Is it possible to transform the interpretation of that original script ´by replacing missing values by 'UNKNOWN'?

[maxgro]

you can do a resident load of the tables your binary loaded

binary ...............;

// if you have a table YourTable

NewYourTable:

noconcatenate

load ....................               / / replace null with unknown

resident YourTable;

drop table YourTable;

rename table NewYourTable to YourTable;