Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Text as a column in nested applymap

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?

1 Solution

Accepted Solutions
Highlighted

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')

)

 

View solution in original post

8 Replies
Highlighted

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')

)

 

View solution in original post

Highlighted
Creator
Creator

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:

QV_Sample_7.png

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.

Highlighted
Specialist II
Specialist II

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 ;

 

Highlighted
Partner
Partner

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?

Highlighted
Partner
Partner

Jwaligora,

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

Highlighted
Partner
Partner

@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.

Highlighted

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.

Highlighted
Creator
Creator

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:

QV_Sample_11.png

It outputs:
QV_Sample_12.png

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

Thanks
J.