topic Re: Rename blank fields in QlikView

Rename blank fields

pauldamen — Wed, 02 Jan 2013 10:40:59 GMT

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

Re: Rename blank fields

hic — Wed, 02 Jan 2013 10:49:24 GMT

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

Re: Rename blank fields

Wed, 02 Jan 2013 10:55:49 GMT

Dear,

you can use Henric procedure.

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

Re: Rename blank fields

pauldamen — Wed, 02 Jan 2013 11:04:42 GMT

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

Re: Rename blank fields

Wed, 02 Jan 2013 11:13:28 GMT

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.

Re: Rename blank fields

pauldamen — Wed, 02 Jan 2013 11:17:09 GMT

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

Re: Rename blank fields

Wed, 02 Jan 2013 11:30:58 GMT

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.

Re: Rename blank fields

pauldamen — Wed, 02 Jan 2013 11:39:39 GMT

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;

Re: Rename blank fields

Wed, 02 Jan 2013 11:44:04 GMT

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.

Re: Rename blank fields

pauldamen — Wed, 02 Jan 2013 12:49:23 GMT

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