load missing data into existing data

[#1]

Hi,

The situation is like this.
We have a file with the information about our employees.
In that file there is a field "sector", with the sector where they are working.
Due to some errors of the people who made this file, there are some employees where the field "sector" is empty.
Now i received a file with in 1 field the names of the employees and in the second field the sector where thay are working.
(This file only contains the names of the employees where the sector-field is empty in the original file).

So now I need to know if there is a way to load this data into our existing data.
I should find a way to go trough our existing file and check if the field "sector" is empty.
If so, then it should check if this name exists in my file with missing data, and fill in the sector that belongs to that name.

Anyone who knows an answer to my question?

Thanks in advance!

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

I'd do something like the following

EmployeeSectorMap: // table coming from your excel fileMAPPING LOAD @1 AS Employee, @2 AS SectorFROM file.xls (biff, no labels, table is Sheet1$); // the code is generated by the "Table Files..." button Employees:LOAD Employee, ApplyMap('EmployeeSectorMap', Employee, Sector) AS SectorFROM Employees.qvd (qvd); // file where first load of employees was stored

So you will get the Sector loaded in the map table if there is a correspondence, otherwise, the already existing Sector (bold) will be loaded.

Hope that helps.

[brenner_martina]

HI,

you can use the if-then-else statement to find and mark the missing sectors:

Load Field, ... ,
If(IsNull(Sector),'Missing Sector',sector) as sector,
Fields,...
From ...;

Pardon, I had no more time to enter my info, a customer wanted help!

[deepakk]

hi,

i would suggest you to combine both data.

eg:

test:

load emp, sector from original;

concatenate

load emp, sector from table;

noconcatenate

load *resident test where len(trin(sector))>0;

drop table test;

[#5]

Thx for the quick replies!

@Deepak: Your method would work if i had only the name and sector of our employees in my original file.
But there is a lot more information in it like address, telephone number, etc...
And only the sector is empty sometimes, so I literally need to fill the empty gaps in my original file.

@Miguel: This seems a very nice method, I did not know the applyMap statement.
But still there seems to be somethin wrong...
I get double names of those who are in the file with the missing sectors, and only the sector is filled for those who are inside the file with the missing sectors. So those who had a sector filled in next to their names in the original file, are now all empty..

[Miguel_Angel_Baeyens]

Hi,

Applymap may take three parameters: first is the name of the table where the map is, second is the field which correspondence you want to know, third is value by default. I set field Sector as value by default for those names that don't have a correspondence, so I shouldn't care about null or empty values. Code can be changes to

If(Len(ApplyMap('EmployeeSectorMap', Employee)) = 0, Sector, ApplyMap('EmployeeSectorMap', Employee)) AS Sector

But that what's third parameter of ApplyMap is for...

Hope that helps.

[#7]

Got it working!

After all, it did work wit the first method from Miguel, but I just had to do a Mapping Load instead of a normal load.

Thanks all for the help!

[Miguel_Angel_Baeyens]

I'm kind of embarrased, what a mistake. Yes, that's I was trying to say, but I missed the MAPPING. I've checked the code.

Thanks for noting!