ApplyMap not working

[Kain_F]

Hi everyone, 

I am trying to automatically transform my table into a fact table based on known dimensions that are in the table.

Before doing this I create a D_table for each dimension. 

let dimensionlist = 'dimension1-dimension2-dimension3';
let end = SubStringCount('$(dimensionlist)','-')+1;

for i = 1 to $(end)
	
    let current_dimension = SubField('$(dimensionlist)','-',$(i));
    let TEMP_TABLE = 'D_$(current_dimension)_TEMP';
    let TABLE = 'D_$(current_dimension)';
   
    $(TEMP_TABLE):
    NoConcatenate
    load 
    	distinct
	(subfield(purgechar(lower($(current_dimension)),'"[]'),',')) as $(current_dimension).map
   	from [MySource.qvd] (qvd);
    
    $(TABLE):
    NoConcatenate
	Load
	RowNo() as ID_$(current_dimension),
    	$(current_dimension).map
	resident $(TEMP_TABLE);
	drop Table $(TEMP_TABLE);   
next;

I then try to get my facttable as follows

TEMP:
load 
	*,
    RowNo() as index_column,
    (subfield(purgechar(lower(dimension1),'"[]'),',')) as dimension1.map,
    (subfield(purgechar(lower(dimension2),'"[]'),',')) as dimension2.map,
    (subfield(purgechar(lower(dimension3),'"[]'),',')) as dimension3.map,
from [MySource.qvd] (qvd);


for i = 1 to $(end)
	
    let current_dimension = SubField('$(dimensionlist)','-',$(i));
    let TABLE = 'D_$(current_dimension)';
    
    $(TABLE).MAP:
    Mapping Load
    	*
    Resident $(TABLE);
    
    
    left join (TEMP)
      load
      	  index_column,
      	  ApplyMap('$(TABLE).MAP',text($(current_dimension).map),'failure to map') as ID_$(current_dimension)
      Resident TEMP;
   drop fields $(current_dimension).map,$(current_dimension) from  TEMP;    
next;

However, even though i am generating a mapping table on each iteration of the dimension needed, the result in the final TEMP table for the columns ID_dimension1, ID_dimension2,I D_dimension3 is the default value of the ApplyMap function being 'failure to map' in my case. 

I cant seem to find a reason why I dont get the actual ID values for these dimensions.

Thanks a lot 😄

[rubenmarin · Accepted solution]

Hi, mapping table has two field: first is the values to search and the second is the value to return. I think the mapping table is using ID_$(current_dimension) as the value to search, but in applymap you are looking by text($(current_dimension).map), which is the value to return, not the value to search.

Also the ID created by RowNo() would be a number, and in applymap the Text() fucntion will make a differnt format of the value to search.

[Kain_F · Accepted solution]

Hi Ruben,

Switching out the ID and the mapping column in the mapping table did the trick 🙂

for i = 1 to $(end)
	
    let current_dimension = SubField('$(dimensionlist)','-',$(i));
    let TABLE = 'D_$(current_dimension)';
    
    $(TABLE).MAP:
    Mapping Load
    	$(current_dimension).map,
        ID_$(current_dimension)
    Resident $(TABLE);
    
    
    left join (TEMP)
      load
      	  index_column,
      	  ApplyMap('$(TABLE).MAP',text($(current_dimension).map),'failure to map') as ID_$(current_dimension)
      Resident TEMP;
   drop fields $(current_dimension).map,$(current_dimension) from  TEMP;    
next;

Thanks for the help 😄