Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gdilullo
New Contributor III

Mapping Table Excel

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:

foglioex.PNG.png


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

Tags (1)
10 Replies
Not applicable

Re: Mapping Table Excel

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
New Contributor III

Re: Mapping Table Excel

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

Not applicable

Re: Mapping Table Excel

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_michaelid
Honored Contributor II

Re: Mapping Table Excel

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
New Contributor III

Re: Mapping Table Excel

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


Giuseppe

jason_michaelid
Honored Contributor II

Re: Mapping Table Excel

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
New Contributor III

Re: Mapping Table Excel

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_michaelid
Honored Contributor II

Re: Mapping Table Excel

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_michaelid
Honored Contributor II

Re: Mapping Table Excel

I wrote:


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


Just switch that to


AS Raggruppamento

Community Browser