Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Kain_F
Contributor III
Contributor III

ApplyMap not working

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 😄

Labels (1)
2 Solutions

Accepted Solutions
rubenmarin

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.

View solution in original post

Kain_F
Contributor III
Contributor III
Author

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 😄  

View solution in original post

2 Replies
rubenmarin

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
Contributor III
Contributor III
Author

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 😄