Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
In practice:
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?
Thanks
Beppe
Hi Giuseppe,
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.
Dave
I think that the problem is in the presentation. In this way is loaded the excel table:
RIMAP:
LOAD
Cond.Contrattuale as X_CondContrattuale,
Posizione as X_Posizione,
AreaProfessionale as X_AreaProf,
Raggruppamento as X_Valore
From
Report_trimestrale_map_3d.xls
(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
AreaProfessionale=X_AreaProf),
X_Valore, NULL())
where:
- DWH_CondContrattuale
- DWH_Posizione
- AreaProfessionale
are the fields taken from DB.
Tnks
Giuseppe
Hi Giuseppe,
Have you thought of using a Mapping Load and ApplyMap() in the loadscript, rather than trying to do the mapping in the pivot table?
mapTable:
Mapping Load
[Cond.Contrattuale] & '.' & Posizione & '.' & AreaProfessionale as tripleKey,
Raggruppamento
FROM
Report_trimestrale_map_3d.xls
(biff, embedded labels, table is Foglio1$);
dbTable:
LOAD DWH_CondContrattuale,
DWH_Posizione,
AreaProfessionale,
ApplyMap('mapTable',DWH_CondContrattuale & '.' & DWH_Posizione & '.' & AreaProfessionale,Null()) as Raggruppamento
FROM dbtable;
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.
Kind Regards,
Dave
If I understand you correctly then you do need a mapping table. Try:
Map_AreaProf:
MAPPING LOAD
Cond.Contrattuale & '/' & Posizione & '/' & AreaProfessionale
Raggruppamento
FROM
Report_trimestrale_map_3d.xls
(biff, embedded labels, table is Foglio1$);
Data:
LOAD
DWH_CondContrattuale
,DWH_Posizione
,AreaProfessionale
,ApplyMap('Map_AreaProf',DWH_CondContrattuale & '/' & DWH_Posizione & '/' & AreaProfessionale,'Unmapped') AS Valore
;
SQL SELECT....;
Hope this helps,
Jason
Can you explain why you would use this operator "/"?
Giuseppe
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!!)
AA/BB/CC
AAB/BC/C
Hope this helps,
Jason
OK,
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
- etc.....
that is the grouping (Raggruppamento) in the excel table. With a mapping table I lose this information!
Thanks
Giuseppe
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....
I wrote:
,ApplyMap('Map_AreaProf',DWH_CondContrattuale & '/' & DWH_Posizione & '/' &AreaProfessionale,'Unmapped') AS Valore
Just switch that to
AS Raggruppamento