Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
trishita
Contributor III

How can I use a field in table A as a measure in table b with or without mapping?

map.JPG

I want to map the 'Kasse' and 'Share%' column of the above table A(excel sheet) to 'Depot' of another existing table B and then want to multiply the 'Share%' with the 'Total commit' field in the table B.

How can I use this in qliksense? Do I need mapping and applymap function?

For ex,

Map_A:
Mapping
LOAD

Share%,
Kasse
FROM [Mapping_Pensionseinrichtungen.xlsx]
(ooxml, embedded labels, table is Tabelle1)

B:

Load 

* ,

Applymap(Map_A, Depot) as Pensionseinrichtungen,

Share%*[Total Commit] as    Real Total Commit

FROM B

How can I use the Share% in table A as a measure in table b with or without mapping?

Should I use inline mapping function or resident/preceding load?

 

7 Replies
Partner
Partner

Re: How can I use a field in table A as a measure in table b with or without mapping?

On face value it should work (but order is wrong it should be load Kasse, %share). Use resident load if you can
But questions
what would be the relation ship between this table and the other table if it is one kasse is related one/many in other table; you dont need mapping at all. make sure the column names are the same and use the same measure
is this table that showed above a dimension table? i.e. this is where it defines share %. if it is 1 share % for 1 kasse then essential you can consider this as a dimension table
trishita
Contributor III

Re: How can I use a field in table A as a measure in table b with or without mapping?

i want to modify my report script actually...The tables A nad B are already joined and the kasse is also previously mapped  to depot.But I want to add the share as a new column in the exisiting table A so that I dont have to hardcode it and use it as a variable mapped to the correct Kasse.

if(match(subfield(%HPP_Summary_List,'_',1),'HPPD'),
If(Match([Pensionseinrichtung], 'MER'),Sum( [Total Commit] *0.0099),
If(Match([Pensionseinrichtung], 'CONTI'),Sum( [Total Commit] *0.02,
If(Match([Pensionseinrichtung], 'HPF EK'),Sum( [Total Commit] *0.0008,
sum([Total Commit]*.08)
))))

I dont want to use this shares as a hard code but use a fiield Share%  in this regard.What could be the optimal solution

Partner
Partner

Re: How can I use a field in table A as a measure in table b with or without mapping?

I cannot help in detail without knowing more about you data model etc.
But on face value apply map should work.

trishita
Contributor III

Re: How can I use a field in table A as a measure in table b with or without mapping?

i want to use the share% field which is contained in the comment 

Applymap(Map_A, Depot)  where Map_A contains Kasse and also Share%. How can I use the share% field?

Partner
Partner

Re: How can I use a field in table A as a measure in table b with or without mapping?

Not clear what you mean by Map_A contains Kasse and also Share%.

I think there maybe a misunderstanding of what a map is.
Map is a key value field
i.e. you map a key e.g. MER to a a value (share) 0.0099
so if you do applymap('Map-A','MER') you will back 0.0099

trishita
Contributor III

Re: How can I use a field in table A as a measure in table b with or without mapping?

What if applymap('Map-A','MER') 

Can Map-A(from table A) contain both values of Kasse Name and Also share? 

If I want to map INTO MER into a new destination table, should i have to create this new field or the field will be created automatically?

For example the share is not present in table B but the kasse name is present.I want to retrieve values of share from table A to table B

Partner
Partner

Re: How can I use a field in table A as a measure in table b with or without mapping?

A map is a key value pair. I.e. you lookup by the key to get the corresponding value.
applymap('Map-A','MER') here MER is the key and share could be the value.
Not clear what you mean. I suggest you check out either the help page or somewhere in YouTube to check how applymap is used