topic Re: Question about mapping in QlikView

Question about mapping

Sun, 04 Sep 2011 13:03:33 GMT

Hi,

Please i have a question about mapping

i have field called Activity

which has the values of 1,2,3,4

i want to represent them with names A,B,C,D

so i made mapping

LOAD * Inline

[

Activity , Name

1,A

2,B

3,C

4,D

];

when i select Name it reflects to the Activity

but when i used the Activity in the expression it doesn't reflect anything

hope the attached example clarify the problem

Re: Question about mapping

Jason_Michaelides — Sun, 04 Sep 2011 13:20:48 GMT

Hi Mona,

I've fixed this up for you and offered an alternative using ApplyMap() - just comment out the top part of the script and uncomment the second part.

Hope this helps,

Jason

Re: Question about mapping

swuehl — Sun, 04 Sep 2011 13:25:45 GMT

Mona_qlik,

if you want to keep the tables not linked, you could just remove the second load from the script and change your expression in the table to

=sum(if(Activity=NewActivity,Value))

Table1:

LOAD * Inline

[

Activity,Value

1,200

1,400

1,300

2,50

2,300

3,340

3,690

4,300

4,90

];

//LOAD Activity as ActivityNew

//Resident Table1;

LOAD * Inline

[

ActivityNew,Name

1,A

2,B

3,C

4,D

];

Regards,

Stefan

P.S. Or use a mapping load with your second table and applymap in your first (you need to change the order of your tables though)

P.P.S: I would prefer Jasons second solution (essentially what I meant with my first P.S., just saw his after comitting my post)

edited by swuehl

Question about mapping

Sun, 04 Sep 2011 13:40:29 GMT

thanks Jason for your help

but in my case as i attached iun the document i have to create another field resident the first one but this not working in the expression

i.e the field that's not reflected is the ActivityNew not the Activity

Hope the problem now is clear

Question about mapping

Jason_Michaelides — Sun, 04 Sep 2011 13:48:24 GMT

Sorry Mona but I don't really understand what you're trying to achieve. Are you saying you want 2 fields with exactly the same values in them, one called Activity and one called ActivityNew?

Question about mapping

swuehl — Sun, 04 Sep 2011 13:53:39 GMT

I also don't really get what you want Mona.

If you need to keep your data model (maybe because we dont't understand your whole picture), you could use

=Sum({$<Activity={'$(=only(ActivityNew))'}>}Value)

in your table to get your / some / any results (because I ... see above).

Regards,

Stefan

Question about mapping

Sun, 04 Sep 2011 14:24:58 GMT

Sorry Stefan and Jason

i know that my problem is complicated so above i attached a simplified example but not the real case so you can't understand what i want to do

Actually the last expression Stefan has posted is working in the case that (A) For example is reflected to (1)

which is

=Sum({$<Activity={'$(=only(ActivityNew))'}>}Value)

but what if the mapping for Name (A) is related with 2 activities (1 and 2 for example)

and i want the above expression to work with them??

Re: Question about mapping

Jason_Michaelides — Sun, 04 Sep 2011 14:28:02 GMT

If I understand you correctly (and I'm afraid I'm still not sure I do!) then both my solutions would still work in this case. Just add the second mapping to the Map table:

Activity_Map:
LOAD * Inline
[
Activity,Name
1,A
2,B
3,C
4,D
5,A
6,A
7,A
];

Jason

Question about mapping

swuehl — Sun, 04 Sep 2011 14:30:49 GMT

Also I am just guessing:

=Sum({$<Activity={'$(=concat(ActivityNew,chr(39)&','&chr(39)))'}>}Value)

allows to assign multiple ActivityNew values to be assigned to Activity.

Question about mapping

Sun, 04 Sep 2011 14:36:20 GMT

yes Jason i did it

but now i want an equal expression to the one Stefan posted

=Sum({$<Activity={'$(=only(ActivityNew))'}>}Value)

to be used in the case of 2 Activities has the same name

is that clear?

Question about mapping

Jason_Michaelides — Sun, 04 Sep 2011 14:38:56 GMT

Sorry Mona - if you'd like to expand on what you are trying to achieve and why you're taking the approach you are then maybe we can help more. I'm afraid I really don't understand what you're trying to do. Does Stefan's expression above do it for you?

Jason

Question about mapping

Sun, 04 Sep 2011 14:48:08 GMT

Thanks Alot Stefan

it's working

but if u please explain what's meant by ch(39) as i can't undersatnd your expression

but it's working correctly,just i need to understand

Question about mapping

swuehl — Sun, 04 Sep 2011 14:58:58 GMT

Mona,

the expression

'$(=concat(ActivityNew,chr(39)&','&chr(39)))'

will create a list of the comma separated ActivityNew values, each in quotes (chr(39) is calling the function to return ' with given ASCII code 39 for ' ). The list will then be taken as element_set by the set analysis expression, assigning the values to the field Activity.

I noted also that adding a DISTINCT before ActivitiyNew might be a bit more elegant.

Hope this solves your issue.

Regards,

Stefan

P.S: In cases like that, it might be good to spend a few words on your "big picture", this may allow to disregard some solutions upfront and may save some time

Question about mapping

Jason_Michaelides — Sun, 04 Sep 2011 15:31:50 GMT

Nicely done Stefan

Mona - please make sure you credit Stefan for the answer by marking it as "correct".

Jason

Question about mapping

swuehl — Mon, 05 Sep 2011 14:46:28 GMT

Just to add a note: I just found a much easier solution:

Sum({$<Activity= p(ActivityNew) >}Value)

Regards,

Stefan

Question about mapping

Tue, 06 Sep 2011 10:22:55 GMT

Thanks so much Stefan and Jason

i will try your second solution Stefan

Best Regards,

Mona