topic Re: Combining povit columns and comparing data in povit in QlikView

Combining povit columns and comparing data in povit

Anonymous — Sun, 17 Apr 2016 05:55:41 GMT

Hi Team,

Good day

please look on bellow issue

How to combine the columns expression into one column?

Re: Combining povit columns and comparing data in povit

swuehl — Sun, 17 Apr 2016 10:56:16 GMT

And your pivot table looks original like...?

(I am not talking about a screenshot, but a full description of dimensions, expressions, settings, data model)

It's currently hard to understand where you are coming from.

Please post a sample QVW with some sample data and your expected result.

Re: Combining povit columns and comparing data in povit

Colin-Albert — Sun, 17 Apr 2016 12:25:44 GMT

Can you describe how the combination column should be calculated?

Your screen shots omit the column headings.

Is it

(Column1 * 10000) + (Column2 *1000) +(Column3 * 100) + (Column4 * 10) + Column5 as Combined

How are you deriving the final numbers and description - is this from another table?

Re: Combining povit columns and comparing data in povit

Anonymous — Mon, 18 Apr 2016 05:16:32 GMT

By using the column or column no to combine the column in the pivot table and we get the result

Re: Combining povit columns and comparing data in povit

swuehl — Mon, 18 Apr 2016 06:49:55 GMT

Something like this?

=Column(1)*10000+Column(2)*1000+Column(3)*100+Column(4)*10+Column(5)

Or are these columns dimensional columns, and you are using only 1 expression?

It would really be helpful if you could post a small sample QVW that demonstrates your setting, also demonstrates how your lookup table looks like.

Re: Combining povit columns and comparing data in povit

Anonymous — Mon, 18 Apr 2016 06:56:41 GMT

Thanks all for sheering your ideas,

swuehl and Colin Alber‌

i am attaching sample QVW file with my requirements and thoughts

please look on that .let me know

regards,

Bobby

Re: Combining povit columns and comparing data in povit

swuehl — Mon, 18 Apr 2016 07:06:59 GMT

Maybe like this?

Or could you do the whole thing in the script? That would be possible if the combined column wouldn't change for a CITY (like in your sample).

Re: Combining povit columns and comparing data in povit

sunny_talwar — Mon, 18 Apr 2016 08:23:37 GMT

Try this may be:

=Aggr(If(A&B&C&D * 1 = Code, status), Code, CITY)

=Aggr(If(A&B&C&D * 1 = Code, [out of 10]), Code, CITY)

Re: Combining povit columns and comparing data in povit

Anonymous — Mon, 18 Apr 2016 17:22:25 GMT

Hi sunindia ,swuehl

Excellent but i need some more help

but now i update my QVW file with my new requirements.please look on attached qvw file

note:in dimensional level i have more level drill down options then that time i want see last level matching data as well

let me know if you need more explanation

Regards,

Bobby

Re: Combining povit columns and comparing data in povit

sunny_talwar — Mon, 18 Apr 2016 17:26:01 GMT

This may be:

=Aggr(If(if(A=1,'5',if(A=0,'6'))&if(B=1,'5',if(B=0,'6'))&if(C=1,'5',if(C=0,'6'))&if(D=1,'5',if(D=0,'6')) * 1 = Code, status), Code, CITY)

=Aggr(If(if(A=1,'5',if(A=0,'6'))&if(B=1,'5',if(B=0,'6'))&if(C=1,'5',if(C=0,'6'))&if(D=1,'5',if(D=0,'6')) * 1 = Code, [out of 10]), Code, CITY)

Re: Combining povit columns and comparing data in povit

swuehl — Mon, 18 Apr 2016 17:45:00 GMT

With more complex expressions, this might get a little hard to read.

You can also use a pick(match()) combination and using Column()

=Pick(

Match(Column(1)&Column(2)&Column(3)&Column(4) , $(=Concat(Code,','))),

$(=Concat(Chr(39)&status&chr(39),',',Code))

)

Note that your code table shows two entries for code 6565, average and good. Is this intended?

edit:

Or using your expression labels:

=Pick( Match(H&E&F&G , $(=Concat(Code,','))),

$(=Concat(Chr(39)&status&chr(39),',',Code))

Re: Combining povit columns and comparing data in povit

Anonymous — Tue, 19 Apr 2016 11:41:59 GMT

Hi Sunny T,

coud you pleae give some explanation of

=Aggr(If(if(A=1,'5',if(A=0,'6'))&if(B=1,'5',if(B=0,'6'))&if(C=1,'5',if(C=0,'6'))&if(D=1,'5',if(D=0,'6')) * 1 = Code, status), Code, CITY)

*1 means it wil convert to text correct me i said any wrong?

Re: Combining povit columns and comparing data in povit

sunny_talwar — Tue, 19 Apr 2016 11:42:59 GMT

Yes the idea is to convert the text to number by multiplying it by 1

Re: Combining povit columns and comparing data in povit

Anonymous — Tue, 19 Apr 2016 12:23:13 GMT

hi sunny ,

i tired your expression in my dashboard

bellow screen shot please observe

here i if select Code in list box then i am able to see "status" and "out of 10" columns data.

in pivot table i have city ,place,land mark,area like these kind of drill down dimensions

here when i select Code that time why Status and OUT of 10 coulms data will be display?

Re: Combining povit columns and comparing data in povit

sunny_talwar — Tue, 19 Apr 2016 12:37:33 GMT

Not sure I understand the difference between the screenshots. I mean are these taken from two different applications? same application different charts? What's the difference?

Re: Combining povit columns and comparing data in povit

Anonymous — Tue, 19 Apr 2016 12:53:37 GMT

yes perfectly correct ...

same appliction different charts..one is povit and second one is list box

for understanding the difference i attached screen shot

screen shot 1 :if i select "CODE" column values i am able to see the data in Status and Out of 10 Column data

screen shot 2:if i unselect "CODE" column values i am not able to see the data in Status and Out of 10 Column data

but i dont see Iist box in my application i want only pivot with our any select any code column like bellow

i need out put like above screen shot only .

regards,

bobby

Re: Combining povit columns and comparing data in povit

swuehl — Tue, 19 Apr 2016 13:03:11 GMT

Might be that you are running into one of the pitfalls using aggr()

Pitfalls of the Aggr function

e.g. grain mismatch or missing outer aggregation.

We would need to take a closer look at your data model to tell you more.

Have you tried what I've suggested above using pick(match() ), i.e. not using the aggr() function?

Re: Combining povit columns and comparing data in povit

Anonymous — Tue, 19 Apr 2016 14:38:25 GMT

yes i tired that but sorry to say it"s not useful for me

and can we try VLOOKUP for this .but i am nout sure vlookup is working in qik view or not

like:

VLOOKUP(CONCATENATE(Column(1)&Column(2)&Column(3)&Column(4)),'Code'), "Unexplained Result")

can we achievable using Vlookup ?

Regards,

Bobs

Re: Combining povit columns and comparing data in povit

swuehl — Tue, 19 Apr 2016 14:44:44 GMT

No. No VLOOKUP available in QV chart expression.

Could you explain why the other approach was not useful for you? Maybe this helps in understanding your other issue as well.

Or just create a small sample file, so we can stop guessing around?

Re: Combining povit columns and comparing data in povit

Anonymous — Mon, 30 May 2016 11:54:39 GMT

For more details please look the bellow link

mapping Pivot column Test to another table column Test

Regards,

Bob