Text as a column in nested applymap

[sjhussain]

Hello,

I am trying to build a key value using applymap.

What we want to do is to pass the result of one applymap which would be a column name into another applymap.  

I have tried it but it seems like it is not understanding that the result of the applymap.

ApplyMap('MAP_THRESHOLD1_SYNDROME',ShortName,Null()) returns "REGIONS" which is a column name and i want to use the value in the REGIONS column to be passed into the MAP_THRESHOLD1_CODE applymap. 

ShortName & '-' & applymap('MAP_THRESHOLD1_CODE',ApplyMap('MAP_THRESHOLD1_SYNDROME',ShortName,Null()),'n/a') & '-' &
ApplyMap('MAP_Threshold3_CASES',ShortName,Null()) AS KEY_CASES,

How would i do that?

[rubenmarin · Accepted solution]

Hi, if there are not many fields you try changing it to a nested if or pick(match(:

Pick(Match(ApplyMap('MAP_THRESHOLD1_SYNDROME',ShortName,Null()), 'REGIONS, 'COUNTRIES')

  ,applymap('MAP_THRESHOLD1_CODE', REGIONS,'n/a')

  ,applymap('MAP_THRESHOLD1_CODE', COUNTRIES,'n/a')

)

[jwaligora]

You can try 'preceding load' to allow the first ApplyMap to process before applying the other.

Here's an example:

Countries:
Mapping
Load * Inline [
	City, Country
	Brussels, Belgium
	Amsterdam, Netherlands
	Frankfurt, Germany
	Berlin, Germany
	Cairo, Egypt
	Rome, Italy
	Milan, Italy
	Lagos, Nigeria
	Bamako, Mali
];

Continents:
Mapping
Load * Inline [
	Country, Continent
	Belgium, Europe
	Netherlands, Europe
	Germany, Europe
	Egypt, Africa
	Italy, Europe
	Nigeria, Africa
	Mali, Africa
];


load
	*,
	ApplyMap('Continents', Country, 'Other Continent') as Continent
;

load 
	*,
	ApplyMap('Countries', City, 'Other Country') as Country
;

Data:
Load * Inline [
	ID,City
	2,Berlin
	3,Prague
	4,Cairo
	5,Brussels
	6,Rome
	7,Milan
	8,Lagos
	9,Bamako
];	

The output is:

But you can opt to load specific fields instead of "*" in the preceding load OR just drop the columns after. 

J.

Oops...I forgot to map Prague.

[qliksus]

Maybe use a precedent load

load

* ,

ShortName & '-' & applymap('MAP_THRESHOLD1_CODE',region,'n/a') & '-' &
ApplyMap('MAP_Threshold3_CASES',ShortName,Null()) AS KEY_CASES,

load

*,

ApplyMap('MAP_THRESHOLD1_SYNDROME',ShortName,Null()) as region

FRom table ;

[sjhussain]

Rubenmarin,

Initially i have the following dimensions:

REGION/NATIONALITY/GENDER which might also change.  Your solutions looks feasible.  Instead of pick match is there a way we can have it dynamically rather than hardcoding the dimension names?

[sjhussain]

Jwaligora,

we wanted dynamic loading of column names and then get the value of it.  The example you provided is hardcoded column names.

[sjhussain]

@qliksus  - i tried it but it did not work.  I think it is unable to recognize it a column.  It is recognizing it as text and it is not working.

[rubenmarin]

Hi, I don't see how to make this dynamic, I tried and it always use the value retrieved as text, not as a field name, maybe other users comes with that idea.

To have it a bit more centralized you can use variables:

SET vField1 = REGIONS;

SET vField2 = NATIONALITY;

SET vField3 = GENDER ;

Pick(Match(ApplyMap('MAP_THRESHOLD1_SYNDROME',ShortName,Null()), '$(vField1)', '$(vField2)', '$(vField3)')

  ,applymap('MAP_THRESHOLD1_CODE', $(vField1),'n/a')

  ,applymap('MAP_THRESHOLD1_CODE', $(vField2),'n/a')

  ,applymap('MAP_THRESHOLD1_CODE', $(vField3),'n/a')

)

Maybe knowing the requierements you can reach a formula to fill vField1, vField2... and even doing a bucle to create the "pick(Match(" syntax from a list of fields, if neccesary I can take a look when I have more free time than now.

[jwaligora]

I was clearly slow on the uptake, so I'm gonna try again using Rubenmarin's response as the foundation. 

The code below takes its cue from the MAP_THRESHOLD1_SYNDROME map, where the output Column Names can vary both in count and in value. 

/* 
This is an equivalent to the MAP_THRESHOLD1_SYNDROME, fist loaded as a Table 
and later transformed into a Map, once extraction of pertinent variables is complete.
*/
ColNamesTab:
Load * Inline [
	SomeFld,ColName
	A, Gender
	B, Nationality
	C, Region
	D, Region
	E, Region
	F, Gender
];

/*
Exctraction of strings to be stored in variables
*/
StringsTab:
Load 
	CONCAT(DISTINCT '''' & ColName & '''' , ',') as ColNamesString,
	CONCAT(DISTINCT 'applymap(''ColValMap'',' & ColName &' ,''n/a'')',',') as MapsListString 
resident ColNamesTab;

/*
Storing of strings into vars
*/
Let vColNames = Peek('ColNamesString') ;
TRACE $(vColNames);

Let vMapsList = Peek('MapsListString') ;
TRACE $(vMapsList);

/*
Turn the Table into the desired Map (aka MAP_THRESHOLD1_SYNDROME)
*/
ColNamesMap:
Mapping
Load distinct * resident ColNamesTab;

/*
Get rid of things no longer needed
*/
Drop Table ColNamesTab;
Drop Table StringsTab;

/*
Create 2nd level map (aka MAP_THRESHOLD1_CODE)
using simplistic values for easy validation
*/
ColValMap:
Mapping
Load * Inline [
	Val, Output
	G, Mapped Gender
	R, Mapped Region
	N, Mapped Nationality
];

/*
Load in the actual data.
*/
Data:
Load * inline [
	ID, SomeFld, Gender, Region, Nationality
	1, A, G, R, N
    2, B, G, R, N
    3, F, G, R, N
    4, E, G, R, N
    5, D, G, R, N
];
 
/*
Transform the data to include the mappings
*/ 
MappedData:
Load distinct
	ID as Record,
	SomeFld as Reference,
	Pick(Match(ApplyMap('ColNamesMap',SomeFld, Null()), $(vColNames))
		, $(vMapsList)
	) as FinalVals
Resident Data
;

Output...

Based on 

ColNamesTab:
Load * Inline [
	SomeFld,ColName
	A, Gender
	B, Nationality
	C, Region
	D, Region
	E, Region
	F, Gender
];

and the original data table:

It outputs:

In case I'm still off the mark, maybe at the very least this will provide some additional ideas to work with.

Thanks
J.