topic Create an exception table in QlikView

Create an exception table

dukane24 — Wed, 06 Dec 2017 16:11:03 GMT

Hello,

Basically I'm trying to find a way to create a table that will selectively override the data in another table under certain conditions.

The tables are something like this:

MainTable:

Employee ID

Manager Name

ExceptionTable:

Employee ID

Manager Name

So if an employee ID is listed in the ExceptionTable, I would want to use the Manager Name listed there instead of the one in the MainTable. I've never had a situation like this, so if anyone has ideas on how it could be accomplished it would be greatly appreciated.

Thank you!

Re: Create an exception table

m_woolf — Wed, 06 Dec 2017 16:15:04 GMT

Use a Mapping Table and the ApplyMap function

Re: Create an exception table

Colin-Albert — Wed, 06 Dec 2017 16:20:30 GMT

Create two mapping tables, one based on the Main Table, the other on the Exceptions.

Then use two nested apply map statements, using the Exception mapping first, then the main mapping if the exception does not return a value.

See these posts

https://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Don't join - use Applymap instead

Re: Create an exception table

m_woolf — Wed, 06 Dec 2017 16:30:35 GMT

Colin,

Can't the Exception table be the mapping Table? When the Main table is loaded, use applymap for the Manager Name field like:

ApplyMap('MappingTableName',[Employee ID],[Manager Name]) as [Manager Name]

Or am I missing something?

Re: Create an exception table

Colin-Albert — Wed, 06 Dec 2017 16:50:22 GMT

Yes, but using a mapping table allows you to specify a value if the Manager data is missing.

If all employees have the manager field populated then your example will work fine.

ApplyMap('ExceptionMgrMap',[Employee ID], ApplyMap('EmployeeMgrMap',[Employee ID],"Not Specified") as [Manager Name]