topic Re: Applymap change in QlikView

Applymap change

Tue, 19 Aug 2014 09:32:17 GMT

Our team's Qlikview developer has changed the underlying data model and moved the DESK field, from a table called TRADES to another one called TRADESPLITS. Therefore my mapping script below no longer works, as i now get the error;

"Field not found - <DESK>"

My script is as follows

MapProduct:

Mapping LOAD

DESKS & SECTORS as Key,

PRODUCT

Resident ProductMappings;

TRADES:

LOAD

ApplyMap('MapProduct',DESK,ApplyMap('MapProduct',SECTOR, Null())) as PRODUCT

Resident Trade_Temp;

Drop Tables Trade_Temp,ProductMappings;

How can I amend this segment of the script to ensure it works? Previously the SECTOR field and DESK field were both in the TRADES table.

The full script is attached for extra clarity if needed.

Re: Applymap change

sujeetsingh — Tue, 19 Aug 2014 09:35:12 GMT

Try applying first Applymap and then use the field in next one

Re: Applymap change

sujeetsingh — Tue, 19 Aug 2014 09:38:30 GMT

Better if you use two Maping tables

Re: Applymap change

martynlloyd — Tue, 19 Aug 2014 09:42:57 GMT

Sounds like you need to join TRADES with TRADESPLITS

TRADES:

LOAD

...

;

Left Join (TRADES)

LOAD

Key1,

Key2,

ApplyMap('MapProduct',DESK,ApplyMap('MapProduct',SECTOR, Null())) as PRODUCT

Resident TRADESPLITS

;

Re: Applymap change

Tue, 19 Aug 2014 10:03:15 GMT

Would this not duplicate the data in the TRADES table though? Currently one trade can have many splits, so is it wise to change that architecture?

Re: Applymap change

Tue, 19 Aug 2014 10:05:57 GMT

What exactly do you mean? One for the SECTOR field and one for the DESK field?

Re: Applymap change

martynlloyd — Tue, 19 Aug 2014 10:13:26 GMT

Woops!

Yes, you need to Load Distinct after the join.

The result will depend on your data in TRADESPLITS.

Marty.

Re: Applymap change

martynlloyd — Tue, 19 Aug 2014 10:18:35 GMT

Hi Alex,

Given the fundamental change to your data model, I'm wondering maybe you should reconsider your approach, and accepting that DESK is now in the other table, you should map PRODUCT in that table.

I take it that both tables exist in the App? Better to use a little bit of memory and keep it simple...

Regards,

Marty.

Re: Applymap change

Tue, 19 Aug 2014 13:19:20 GMT

Hi Alex, As per my understanding, you changes the table structure (field names). So on QVD Loader stage rename the fields into OLD field names to avoid other changes on the Design Layer.