Rename blank fields

[pauldamen]

    Hi all,

If have a list of users in Qlikview, they belong to a department (list comes out of the system, the department is a excel file which is loaded). Now the list from the system contains a lot of names with user that no longer work here, therefor they don't appear under the drill down group I made (department - user) because the people that no longer work here don't have a department.

How can I make a field in the script that looks up users with that don't have a department and then name the field behind this user "Out of Service"? I tried several things but because the fields come from 2 different places I can't seem to find the formula to look in 1 and fill another.

Regards,

Paul

[hic]

If you load the users that exist (in the Excel file) into a Mapping table, you can use this later on to tag the users in a field "Status". Use the function Applymap for the lookup.

ExistingUsers:

Mapping Load User, 'Active' as Status From ExcelFile;

Data:

Load *,

          ApplyMap('ExistingUsers', User, 'Not Active') as Status

          From Database;

An alternative approach is to use the Exists() function to determine whether this user has been loaded before (in the Excel sheet) or not.

HIC

[#3]

Dear,

you can use Henric procedure.

or

write the following peice of code before the tables which your loading.

Nulls_Map:

Mapping Load

Null(),' <Out of Service>'

autogenarate 1;

Map 'department'  Using Nulls_Map;

I hope it will help you.

Thanks,

Mukram

[pauldamen]

Hi Mukram,

I tried you method but instead of filling the blanks it creates a new departement called Out of Service with no name in front. The fields behind the names of the user which are out of service are still showing -

Regards,

Paul

[#5]

Dear Paul.

did you tried Henric method?

if still your not getting the expected result.

then please can you attach a sample file.

Thanks,

Mukram.

[pauldamen]

I tried his method as well but when I put the Apply Map in the SQL select I get the following error when I reload:

"ErrorMsg: 'ApplyMap' is not a recognized built-in function name"

The database part load looks like this, am I doing something wrong there?:

SQL SELECT

"cm_leadscore", fullname, jobtitle, owneridname, accountidname, "new_contact_importancename", "new_contact_ratingname", parentcustomeridname, accountid, statecode , emailaddress1,

ApplyMap('ExistingUsers', owneridname, 'Not Active') as Status

FROM "*****_CRM".dbo.FilteredContact

[#7]

Dear Paul.

I think you need, while loading the table FilteredContact from Database you need to select the Preceding Load

options.

so you can apply the ApplyMap Built in function in between load and sql select.

For example:

FilteredContact:

Load

"cm_leadscore", fullname, jobtitle, owneridname, accountidname, "new_contact_importancename", "new_contact_ratingname", parentcustomeridname, accountid, statecode , emailaddress1,

ApplyMap('ExistingUsers', owneridname, 'Not Active') as Status;

Sql SELECT ......................;

i hope it will work it out.

Thanks,

Mukram.

[pauldamen]

It now gives me the error that there is a syntax error near the word FROM what is wrong there?

LOAD

"cm_leadscore", fullname, jobtitle, owneridname, accountidname, "new_contact_importancename", "new_contact_ratingname", parentcustomeridname, accountid, statecode , emailaddress1,

ApplyMap('ExistingUsers', owneridname, 'Not Active') as Status;

SQL SELECT FROM "****_CRM".dbo.FilteredContact;

[#9]

Dear Paul.

I think something wrong in our sql select statement .

it should be not  like

SQL SELECT FROM "****_CRM".dbo.FilteredContact;

But it should have to have

* before from.

like as follows,

SQL SELECT  * FROM "****_CRM".dbo.FilteredContact;

or you should have the fields you are loading fom the database.

Thanks,

Mukram.

[pauldamen · Accepted solution]

I managed to get it to work.

What I did is split the 2. First I do the regular SQL load. I then added the apply map with a resident load from the sql data. Now it works.

Thanks for your help both!

Regards,

Paul