topic Re: Add 2 fields in mapping in QlikView

Add 2 fields in mapping

Wed, 30 Oct 2013 15:36:39 GMT

Hi there,

I'm like a newbie in Qlikview, and I've got a little something that I can't make. Right now, I've got this :

agent:

MAPPING LOAD * inline [

Old ,New

"MO", "Maladie ordinaire"

"AT", "Accident de travail"

"MA", "Maternité"

"ML", "Longue Maladie"

"MD", "Longue durée"

];

map[raison] using agent;

But the thing is, to build "Maternité", I need more that the code "MA", I need also the code "CP" and the "GP" one. So in this way, the number of MA will add itself to the CP one, and to the GP one, to build the "Maternité" right number.

FYI : the agent table is build that way (for a month, we count the amount of day missed, and why. If there are two reasons, you've got the amount of time for the first reason, and on an other line, the second one)

LOAD

IDF_AGENT,

NUM_ANNEE as num_annee,

NUM_MOIS as num_mois,

count(NUM_JOUR) as NbJourAbsence,

COD_ARRET as raison

RESIDENT

table_calendrier

GROUP BY

IDF_AGENT,NUM_ANNEE,NUM_MOIS,COD_ARRET;

How to do that ? Thanks a lot !

Re: Add 2 fields in mapping

swuehl — Wed, 30 Oct 2013 16:01:20 GMT

Could you please post some sample lines of your data and your requested result for those records?

Map raison using agent;

is just mapping your input values to the values in your mapping tables when storing them to the internal table, it seems hard to apply any additional logic at that point.

Re: Re: Add 2 fields in mapping

Wed, 30 Oct 2013 16:12:25 GMT

So I want smth like that :

IDF_AGENT	NUM_MOIS	NUM_ANNEE	NbrJourAbsence	Raison	Famille
1	2	2012	3	CP	Maternité
1	2	2012	5	MA	Maternité
1	5	2012	25	GM	Grave Maladie
5	12	2011	30	MP	Maladie Professionnelle

I've got 13 differents codes, and I want to regroup them by "family".

Code => Familiy

CP-GP-MA => "Maternité"

GM => "Grave Maladie"

...

The "Map raison using agent;" is something I tried, but it was a failure !

But right now, I'm not able to find the solution for the "Family" row, and I thought it was something in the mapping.

Re: Add 2 fields in mapping

swuehl — Wed, 30 Oct 2013 16:17:11 GMT

If I understood correctly, it should be enough to add the mappings to the mapping table:

agent:

MAPPING LOAD * inline [

Old ,New

"MO", "Maladie ordinaire"

"AT", "Accident de travail"

"MA", "Maternité"

"GP", "Maternité"

"CP", "Maternité"

"ML", "Longue Maladie"

"MD", "Longue durée"

];

Re: Add 2 fields in mapping

Wed, 30 Oct 2013 16:21:19 GMT

But with that, It'll create 3 differents occurrences of Maternité, instead of adding the 3 codes between themselfes.

Re: Re: Add 2 fields in mapping

swuehl — Wed, 30 Oct 2013 16:26:31 GMT

Sorry, I don't get what you mean by adding the 3 codes.

agent:

MAPPING LOAD * inline [

Old ,New

"MO", "Maladie ordinaire"

"AT", "Accident de travail"

"MA", "Maternité"

"GP", "Maternité"

"CP", "Maternité"

"ML", "Longue Maladie"

"MD", "Longue durée"

];

Map Famille using agent;

LOAD IDF_AGENT,

    NUM_MOIS,

    NUM_ANNEE,

    NbrJourAbsence,

    Raison,

    Raison as Famille

FROM

[http://community.qlik.com/thread/97607]

(html, codepage is 1252, embedded labels, table is @1);

This creates a table like you posted above, so you can use Famille to group your data, Codes MA,CP,GP will fall under Maternité.

Re: Add 2 fields in mapping

Wed, 30 Oct 2013 16:29:10 GMT

Let it go, I'm an idiot. I've tried this earlier, but I don't know what was my mistake, in this time it works. Sorry to have disturbed you.