topic Re: Problem empty fields Excel in QlikView

Problem empty fields Excel

Mon, 30 Nov 2015 17:27:59 GMT

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?

Re: Problem empty fields Excel

maxgro — Mon, 30 Nov 2015 17:32:54 GMT

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]

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

Re: Problem empty fields Excel

Tue, 01 Dec 2015 09:55:33 GMT

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

Re: Problem empty fields Excel

Anonymous — Tue, 01 Dec 2015 10:03:52 GMT

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

Re: Problem empty fields Excel

maxgro — Tue, 01 Dec 2015 18:09:12 GMT

post your excel, it would be simpler to help you

Re: Problem empty fields Excel

Wed, 09 Dec 2015 10:07:47 GMT

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'?

Re: Problem empty fields Excel

maxgro — Wed, 09 Dec 2015 19:07:00 GMT

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;