Discussion Board for collaboration on QlikView Scripting.
I have a problem with a mapping table. I have an Excel table that is loaded into a QlikView project, it's used it to create groupings.
The first 3 columns are taken from the DB, the third is assigned in the excel file.
Then: - Loads the data from the DB - Loads the mappings excel - Remaps(Raggruppamento) the triples(Cond.Contrattuale-Posizione-AreaProfessionale) in a pivot table.
The problem is when the same subject A belongs to two different groupings(Raggruppamento)For example:
- Dipendente - Segreteria - Non Professionale - Supporter e amministrativi
- DIpendente - Consulente - Legale - Consulenti e Assistenti
in the pivot table shows only one of the two triples (the most recent in chronological order).Does anyone have an idea? It must use a "mapping table" or something like this?
Is the problem in the presentation (pivot table) or that the data hasn't mapped the second group mapping? Perhaps you could include your loadscript if it's the latter.
I think that the problem is in the presentation. In this way is loaded the excel table:
Cond.Contrattuale as X_CondContrattuale,
Posizione as X_Posizione,
AreaProfessionale as X_AreaProf,
Raggruppamento as X_Valore
(biff, embedded labels, table is Foglio1$);
And this is the expression of dimension (Raggruppamento) in the pivot table:
= if((DWH_CondContrattuale=X_CondContrattuale and
DWH_Posizione = X_Posizione and
are the fields taken from DB.
Have you thought of using a Mapping Load and ApplyMap() in the loadscript, rather than trying to do the mapping in the pivot table?
[Cond.Contrattuale] & '.' & Posizione & '.' & AreaProfessionale as tripleKey,
ApplyMap('mapTable',DWH_CondContrattuale & '.' & DWH_Posizione & '.' & AreaProfessionale,Null()) as Raggruppamento
This is a great primer to using ApplyMap(): http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/
This way your pivot table should work without need for an expression in the dimension.
If I understand you correctly then you do need a mapping table. Try:
Cond.Contrattuale & '/' & Posizione & '/' & AreaProfessionale
,ApplyMap('Map_AreaProf',DWH_CondContrattuale & '/' & DWH_Posizione & '/' & AreaProfessionale,'Unmapped') AS Valore
Hope this helps,
Can you explain why you would use this operator "/"?
Habit really - no real need for it here. Just in case the combination matches another combination for different reasons (very unlikely in this case). E.g.
A key made up from:
AA & BB & CC = AABBCC
Another row might be:
AAB & BC & C = AABBCC
So the two combinations are the same even though they shouldn't be. Inserting a slash between each field value ensures this doesn't happen (unless you have slashes in the data and are VERY unlucky!!)
But this way, how can I load field "Raggruppamento" in the pivot table? With a "applyMap"? I need to be able to see in the pivot:
- Supporter e Amministrativi
- Consulenti e Assistenti
that is the grouping (Raggruppamento) in the excel table. With a mapping table I lose this information!
Well I'm confused. When I wrote my answer yours wasn't there, Dave! It's only just popped up but with a timestamp from ages ago.
Must be a good solution though....
,ApplyMap('Map_AreaProf',DWH_CondContrattuale & '/' & DWH_Posizione & '/' &AreaProfessionale,'Unmapped') AS Valore
Just switch that to