topic Re: Text as a column in nested applymap in QlikView

Text as a column in nested applymap

sjhussain — Wed, 10 Jul 2019 20:13:04 GMT

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?

Re: Text as a column in nested applymap

rubenmarin — Wed, 10 Jul 2019 21:11:03 GMT

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

)

Re: Text as a column in nested applymap

jwaligora — Wed, 10 Jul 2019 21:26:32 GMT

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.

Oops...I forgot to map Prague.

Re: Text as a column in nested applymap

qliksus — Thu, 11 Jul 2019 04:17:01 GMT

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 ;

Re: Text as a column in nested applymap

sjhussain — Thu, 11 Jul 2019 07:04:15 GMT

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?

Re: Text as a column in nested applymap

sjhussain — Thu, 11 Jul 2019 07:05:36 GMT

Jwaligora,

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

Re: Text as a column in nested applymap

sjhussain — Thu, 11 Jul 2019 07:07:06 GMT

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

Re: Text as a column in nested applymap

rubenmarin — Thu, 11 Jul 2019 07:20:53 GMT

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.

Re: Text as a column in nested applymap

jwaligora — Thu, 11 Jul 2019 16:30:13 GMT

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.