topic How do I map field values from multiple data sets into one field name? in QlikView

How do I map field values from multiple data sets into one field name?

scheibercamo — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I am trying to figure out a shortcut for my below issue:

I have multiple data sets, and within each data set there is one column in particular that is named differently, and has different field values, however I need to merge all of these column names and field values into one field so that I can filter my whole data set with just ONE filter. I'd rather not have to write out "IF(Business 101= 'Option AA','Option A') AS Master_Filter" throughout each data set, because this would take a long time (there about data sets, with many more field values) and my mapping might change in the future.

Here's an example:

Data Set 1:

Column Name is "Business 1" and my field values are "Option A", "Option B" and "Option C"

Data Set 2:

Column Name is "Business 101" and my field values are "Option AA", "Option BB" and "Option CC"

Data Set 3:

Column Name is "Business One" and my field values are "Option Ace", "Option Beta" and "Option Charlie"

... when I actually want the field values from Data Set 1 to be the "master"... so in the latter two data sets I want "Option AA" and "Option Ace" to be renamed "Option A". My newly created table would be named "Master_Table".

Hopefully, this would allow to me filter across all three data sets using ONE column name "Master_Table".

Any way to do this without writing out IF THEN statements within each data set? Thanks for any guidance!

Best,

Ricky

Re: How do I map field values from multiple data sets into one field name?

marcelviegas — Mon, 30 Apr 2018 18:42:18 GMT

attach your file to help

Re: How do I map field values from multiple data sets into one field name?

rubenmarin — Mon, 30 Apr 2018 18:42:55 GMT

Hi Rick, to avoid if then you can use a mapping table, check the example in the help:

https://help.qlik.com/es-ES/sense/September2017/Subsystems/Hub/Content/Scripting/MappingFunctions/ApplyMap.htm

As an idea:

mapTable:

Mapping LOAD * Inline [

column1, renamed

OptionAA, OptionA

...

];

Data:

LOAD Applymap('mapTable', Column1, Column1) as Master_Field

...

For that particular values "Left(Columa1, 😎 as Master_field" can work, but I don't know if your real or future data can be those values.

Re: How do I map field values from multiple data sets into one field name?

sasiparupudi1 — Mon, 30 Apr 2018 18:54:21 GMT

Use Applymap

Map_Descriptions:

Mapping load * Inline

[

From, To

Option AA,Option A

Option Ace,Option A

];

Master_table:

Load

F1,

F2,

"Business 1"

From your source

Concatenate(Master_table)

Load

F1,

F2,

Applymap('Map_Descriptions',"Business 101" ,'NA') as "Business 1"

From Your Source 2

Hth

Sasi