topic Re: integrating and formatting multiple crosstables from excel into qlikview in QlikView

integrating and formatting multiple crosstables from excel into qlikview

Fri, 20 Nov 2020 18:02:30 GMT

Hi everyone, complete beginner here. Not sure how to even start, but say I have a large excel table that I am not allowed to change in terms of column position, table format etc. It would look something like this:

ID	Teamname	Championships	Win% 2015	Win% PO 2015	Win% 2016	Win% PO 2016
3rw	Team1	3	20	0	24	0
23r	Teamb	0	60	72	65	66
3re	Team3	9	35	16	27	33

Now what I have done is create a crosstable and show the win% data, but how do I go about separating PO and non-PO values, for example in charts? I guess one way would be to load it with and without, but with more categories in dimensions it doesn't seem efficient. Is it possible to add something like a WHERE LIKE selector when adding dimensions to charts?

Secondly, say I have multiple such import excel files for different sports, but same columns and everything. If I wanted to keep them separate but on the same sheet with a dropdown to select the tables I can just load them the same way right? Or would I need to concatenate/join them? And how do I implement a dropdown to select the tables?

Anyways, the question might be a bit much and I could probably find the answers going through the manual, but because it is still somewhat specific I thought id give it a try. Thanks in advance!

Re: integrating and formatting multiple crosstables from excel into qlikview

marcus_sommer — Thu, 18 Feb 2016 08:35:47 GMT

You need The Crosstable Load to transform your excels into a normal datastructure and I suggest to concatenate them in one table and to create an additionally source-field with filebasename().

- Marcus

Re: integrating and formatting multiple crosstables from excel into qlikview

Thu, 18 Feb 2016 08:51:49 GMT

Thank you for your answer. I already loaded it in a crosstable, my question was more about how to split different categories if you have multiple key attributes. How do I only show the PO win rates or only the regular ones in a chart without creating a crosstable for each case? Is set analysis what I should be looking into?

And would it be possible to get multiple header lines, i.e.

	2015	2015	2016	2016
Team	win%	win% PO	win%	win% PO

so that I can select the year?

Thanks again!

Re: integrating and formatting multiple crosstables from excel into qlikview

Anonymous — Thu, 18 Feb 2016 09:56:00 GMT

Hi Darren,

Indeed this is for me a double cross table, it is not handle out of the box by Qlikview.

What i will suggest is to split it in 2 normal cross table handling your 2 differents values:

- One for Win%

- The second for Win%PO

Maurice

Re: integrating and formatting multiple crosstables from excel into qlikview

Thu, 18 Feb 2016 17:14:42 GMT

It would be pretty inefficient if that was the only way, since the table I'm working with has a lot more categories. Is there no method in calculated dimensions to select the ones that match a string like in vba "win% PO*" and "win% 20*" ?

Thank you for your answer though.

Re: integrating and formatting multiple crosstables from excel into qlikview

marcus_sommer — Fri, 19 Feb 2016 08:13:41 GMT

It's not clear for me waht do you want to do - please provide a small example-app with a few inline-tables.

- Marcus

Re: integrating and formatting multiple crosstables from excel into qlikview

PradeepReddy — Fri, 19 Feb 2016 10:16:44 GMT

Try something like this...

Temp:
Crosstable(Win%_Catg,Value,3)
Load * inline
[
ID,Teamname,Championships,"Win% 2015","Win% PO 2015","Win% 2016","Win% PO 2016"
3rw,Team1,3,20,0,24,0
23r,Teamb,0,60,72,65,66
3re,Team3,9,35,16,27,33
];


Load *,
right(PurgeChar(Win%_Catg,' '),4) as Year,
left(PurgeChar(Win%_Catg,' '),len(PurgeChar(Win%_Catg,' '))-4) as Category
resident Temp;


drop Table Temp;

Thanks,

Pradeep