topic Re: mapping Pivot column Test to another table column Test in QlikView

mapping Pivot column Test to another table column Test

Anonymous — Sat, 28 May 2016 11:03:34 GMT

Hi team,

i am trying to find solution for below requirement since last two months but till now i didn"t get any idea

Here we go

i have one pivot ..in that using Linest_m function i was created some values or Text base upon requirement ..more details please check in Pivot in attached QVW file

after linest_m results i want to map that results to another table "indicater" field ...after mapping the fields .if both vales are mapped correctly then i need to display the another table "Value,text" field details in Pivot table

but here when i was mapping to another table column to the pivot column result ..it showing error i tired so many ideas but no use to me

note:

"Povit column" means combination of Linest_m function results

please suggest me best way of finding this issue solution

Regards,

Bob

Re: mapping Pivot column Test to another table column Test

swuehl — Sat, 28 May 2016 11:24:54 GMT

Have you tried what we've discussed in

Combining povit columns and comparing data in povit

edit:

seems my suggestion still works..

Re: mapping Pivot column Test to another table column Test

Anonymous — Sat, 28 May 2016 13:32:48 GMT

yeah i rember it is also similar but that functionality is not working here ...i tired lot of times with my ideas and your ideas as well ..but no use

Re: mapping Pivot column Test to another table column Test

swuehl — Sat, 28 May 2016 13:37:26 GMT

Have you looked into my sample qvw?

It seems to work for me.

Re: mapping Pivot column Test to another table column Test

Anonymous — Sat, 28 May 2016 13:46:17 GMT

yeah i tired now as well,

you can see the below screen shot as well it wont work for me ..sorry

Re: mapping Pivot column Test to another table column Test

swuehl — Sat, 28 May 2016 13:47:49 GMT

That's not the sample I've uploaded neither the expression I've suggested.

COUNTRY	MovieColletion	Movieprofit	ShowsCount	Column combination	Pick( Match( Column(1)&Column(2)&Column(3), 060,066,560,566), '060','066','560','566' )	=Pick( Match(Column(1)&Column(2)&Column(3), 060,066,560,566), '4','3','5','2' )	Pick( Match( Column(1)&Column(2)&Column(3), 060,066,560,566), 'hit','average','above avg','superhit' )
Ind	5	6	6	566	566	2	superhit
japan	0	6	6	066	066	3	average
php	0	6	0	060	060	4	hit
uk	5	6	6	566	566	2	superhit
us	5	6	0	560	560	5	above avg
Total	5	6	6	566	566	2	superhit

Note that I've used Dollar Sign Expansions in the Pick/Match to create the expressions dynamically from your tables.

Re: mapping Pivot column Test to another table column Test

Anonymous — Sat, 28 May 2016 14:14:00 GMT

hay it"s working fine in our sample files but when i implemented in my main dashboard it"s not working .

i am not able to find reason do you have any idea?

please see my below screen shot

Re: mapping Pivot column Test to another table column Test

swuehl — Sat, 28 May 2016 14:26:23 GMT

That's hard to tell from your screen shot.

I can't even tell from your screenshot if the numbers you want to map even exist in Match() argument list.

Your numbers you want to compare seem to be formatted differently, there is an additional space in some values.

If you need to compare text values, you might need to add single quotes around the values in the Match() function arguments (adjusting the dollar signe expansion's concat function).

Re: mapping Pivot column Test to another table column Test

Anonymous — Sat, 28 May 2016 14:55:24 GMT

yeah you are saying correct in attached dashboard please i am using text

please see my attached QVW file now.

Please don"t convert to integers or numbers i want to map text to text

please make your suggested changes and reply with QVW file

Regards,

Brahma.

Re: mapping Pivot column Test to another table column Test

swuehl — Sat, 28 May 2016 15:17:21 GMT

I believe you need to clarify how your calculated column combination should be mapped to the indicater column.

You calculated combination shows three chars, either one being '5','6' or a space ' '.

You want to map these three char literals to indicater:

resultshow:

Load * inline [

indicater,value,text

566,2,superhit

066,3,average

060,4,hit

560,5,above avg

6 ,1,nostatus

56 ,6,status is pending

];

Qlik by default trims the values, so could you tell if the second last line should actually be ' 6 '?

Re: mapping Pivot column Test to another table column Test

swuehl — Sat, 28 May 2016 15:57:27 GMT

I assumed some formats for your indicater column and ended up with

COUNTRY	MovieColletion	Movieprofit	ShowsCount	Column combination	length of Combination column	Pick( Match( Column(1)&Column(2)&Column(3), '566','066','060','560',' 6 ','56 '), '566','066','060','560',' 6 ','56 ' )	Pick( Match( Column(1)&Column(2)&Column(3), '566','066','060','560',' 6 ','56 '), '2','3','4','5','1','6' )	Pick( Match( Column(1)&Column(2)&Column(3), '566','066','060','560',' 6 ','56 '), 'superhit','average','hit','above avg','nostatus','status is pending' )
Ind	5	6	6	566	3	566	2	superhit
japan		6	6	66	3
php		6		6	3	6	1	nostatus
uk	5	6	6	566	3	566	2	superhit
us	5	6		56	3	56	6	status is pending
Total	5	6	6	566	3	566	2	superhit

There is no indicater value for ' 66', hence no mapping.

Re: mapping Pivot column Test to another table column Test

Anonymous — Mon, 30 May 2016 06:58:27 GMT

Hay swuehl

perfect Solution.

but here i have one thing i want to display else condition like

means when i was comparing Column(1)&Column(2)&Column(3) to indicater text

if both are matching then it will display matching text but if it is not matched then want to display "no indicater"

in bellow code i need to change :

Pick(

Match( Column(1)&Column(2)&Column(3), $(=Concat(chr(39)&indicater &chr(39),',',indicater))),

$(=Concat(Chr(39)&indicater&chr(39),',',indicater))

)

can you help me how to implement this Logic for getting "no indicater"

Regards,

BOb

Re: mapping Pivot column Test to another table column Test

swuehl — Mon, 30 May 2016 08:05:20 GMT

Try something like

Pick(

Match( Column(1)&Column(2)&Column(3), $(=Concat(chr(39)&indicater &chr(39),',',indicater)))+1,

'no indicater',

$(=Concat(Chr(39)&indicater&chr(39),',',indicater))

)

Re: mapping Pivot column Test to another table column Test

Anonymous — Mon, 30 May 2016 08:48:13 GMT

Thanks swuehl,

it"s working for me

regards,

Bob

Re: mapping Pivot column Test to another table column Test

swuehl — Mon, 30 May 2016 08:52:34 GMT

Great!

If your request is resolved, then please consider also closing the other threads with mostly the same topic, like

Combining povit columns and comparing data in povit

Re: mapping Pivot column Test to another table column Test

Anonymous — Mon, 30 May 2016 11:57:16 GMT

yeah swuehl

Done i closed the other thread and in that your code like you said Concatenate...that one i marked as correct answer

Thanks & regards,

Bob