topic Re: ApplyMap not working in QlikView

ApplyMap not working

Kain_F — Fri, 26 May 2023 16:59:30 GMT

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 😄

Re: ApplyMap not working

rubenmarin — Sat, 27 May 2023 07:34:07 GMT

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.

Re: ApplyMap not working

Kain_F — Sun, 28 May 2023 07:33:35 GMT

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 😄