Mapping Table Excel

[gdilullo]

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

[#2]

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

[gdilullo]

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

[#4]

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

[Jason_Michaelides]

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

[gdilullo]

Can you explain why you would use this operator "/"?

Giuseppe

[Jason_Michaelides]

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

[gdilullo]

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

[Jason_Michaelides]

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

[Jason_Michaelides]

I wrote:

     ,ApplyMap('Map_AreaProf',DWH_CondContrattuale & '/' & DWH_Posizione & '/' &AreaProfessionale,'Unmapped')     AS     Valore

Just switch that to

AS Raggruppamento