Problem with ApplyMap

[annabergendal]

I have problem with ApplyMap.

I have quite a large fact table, where I want to add a field from a mapping table.

I guess best practice is to put the mapping table before the fact table, but as mine is based on the calendar I put it in the end after the fact and calendar, and therefore need to load my fact table again when doing the mapping.

Quantity_Per_Period:

mapping load

Quantity_per_period,

Qty_Key

Resident

Quantity;

drop table Quantity;

Fact:

load

*,

Applymap('Quantity_Per_Period', Qty_Key) as Quantity_per_period_map

resident

Data;

My problem is that Qlikview gets overloaded by the time it's about to perform the mapping and freeze... and I don't know why...

Instead of mapping, I also tried to join Quantity_Per_Period to the fact table Data, but the same result...

Do I have a too large fact table? Or what could be the problem? I need to somehow get the field Quantity_per_period in my fact table.

Thanks!

[MK_QSL]

Change like below. ( I think Qty_Key should come first in your Mapping Load Table)

Quantity_Per_Period:

mapping load

Qty_Key,

Quantity_per_period

Resident

Quantity;

[rubenmarin]

Hi Anna,

You sure that's the right order for mapping fields?

ApplyMap searchs Qty_Key in the first Column of mapping table, and it seems to be 'Quantity_per_period' instead of 'Qty_Key'.

Also, remember to drop the 'Data' table or Qv will try to create relations between the two tables.

Regards.

[annabergendal]

Thanks, now it's loading the mapped fact table, but as value in Quantity_per_period_map I get the value from key Qty_Key instead Quantity_per_period... Why is that?

[MK_QSL]

Can you provide your full script along with fieldnames also?

[annabergendal]

Quantity_Per_Period:

mapping load

Datum&'_'&[Material number 1]&'_'&Country_1 as Qty_Key,

If ([Material number 1]=previous([Material number 1]) and Country_1 = previous(Country_1),(If( IsNull( Qty_in_stock ), Peek(Quantity_per_period), Qty_in_stock ) ),Qty_in_stock) as Quantity_per_period

Resident

Quantity;

drop table Quantity;

Fact:

load

*,

Applymap('Quantity_Per_Period', Qty_Key) as Quantity_per_period

resident

Data;

drop table Data;

[Colin-Albert]

You are using previous in your mapping table load, the mapping table will depend on the order that the data is loaded from the Quantity table, but your load script does not have an 'order by' clause.

Also if your mapping table has multiple rows with the same key, the apply map will only return the first marching record, any other records in the mapping table with the same key are ignored by applymap.

[annabergendal]

I use order by in Quantity table so the data is sorted the way I want it to be in order to use Previous.

And the mapping table contains no multiple keys, I checked this table first by making a normal table, and after verifying the data I tried to map it. It must be something wrong with the mapping.

I end up with the same data in Quantity_per_period as in the key Qty_Key...

[rubenmarin]

If applymap doesn't find the key in the map table, the value for Quantity_per_period will be Qty_Key. That will explain why you get Qty_Key in ApplyMap, there is a 3 parameter version of ApplyMap where you can set default value when the key is not in the mapping table.

Assure the Qty_Key in "Applymap('Quantity_Per_Period', Qty_Key) as Quantity_per_period" is similar to "Datum&'_'&[Material number 1]&'_'&Country_1"