topic Re: Mapping Table Excel in QlikView

Mapping Table Excel

gdilullo — Fri, 25 Oct 2013 09:13:36 GMT

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

Re: Mapping Table Excel

Fri, 25 Oct 2013 10:40:38 GMT

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

Re: Mapping Table Excel

gdilullo — Fri, 25 Oct 2013 11:00:45 GMT

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

Re: Mapping Table Excel

Fri, 25 Oct 2013 11:54:27 GMT

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

Re: Mapping Table Excel

Jason_Michaelides — Fri, 25 Oct 2013 12:05:55 GMT

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

Re: Mapping Table Excel

gdilullo — Fri, 25 Oct 2013 13:09:24 GMT

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

Giuseppe

Re: Mapping Table Excel

Jason_Michaelides — Fri, 25 Oct 2013 13:13:55 GMT

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

Re: Mapping Table Excel

gdilullo — Fri, 25 Oct 2013 13:26:06 GMT

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

Re: Mapping Table Excel

Jason_Michaelides — Fri, 25 Oct 2013 13:27:10 GMT

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

Re: Mapping Table Excel

Jason_Michaelides — Fri, 25 Oct 2013 13:48:17 GMT

I wrote:

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

Just switch that to

AS Raggruppamento

Re: Mapping Table Excel

Fri, 25 Oct 2013 14:16:32 GMT

I think it's because I embedded an external link! Great minds and all...