Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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]
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
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
post your excel, it would be simpler to help you
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'?
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;