topic Re: Crosstable with multiple Attribute/Data fields in QlikView

Crosstable with multiple Attribute/Data fields

Thu, 18 Feb 2016 15:19:01 GMT

Is there a way to group attributes and data? If I had a Crosstable like this

id	revenue Q1	profit Q1	revenue Q2	profit Q2
1	23123	213	1323	213
2	5435	3454	43534	3454

And I want to group revenues and profits as separate attributes to display them separately in charts. Now same thing with a lof of different categories, so loading a crosstable for each would be pretty inefficient. Now I guess there might be a way with calculated dimensions or expressions, but I am pretty clueless when it comes to that as I have just started using qlikview, so any help here would be appreciated. Is there anything like regex to match the categories?

Thank you in advance.

-Darren

Re: Crosstable with multiple Attribute/Data fields

sunny_talwar — Thu, 18 Feb 2016 18:14:40 GMT

May be this:

Table:

CrossTable(Quarter, Value)

LOAD id,

[revenue Q1]&'|'&[profit Q1],

[revenue Q2]&'|'&[profit Q2]

FROM

[https://community.qlik.com/thread/206120]

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

FinalTable:

LOAD id,

KeepChar(SubField(Quarter, '|', 1), 'Q1234') as Quarter,

SubField(Value, '|', 1) as Revenue,

SubField(Value, '|', 2) as Profit

Resident Table;

DROP Table Table;

Re: Crosstable with multiple Attribute/Data fields

Gysbert_Wassenaar — Thu, 18 Feb 2016 18:15:14 GMT

You'll have to do it in two steps:

Temp:

CrossTable(Field, Value)

LOAD * FROM ....;

Result:

LOAD

subfield(Field, ' ', 1) as Category,

subfield(Field, ' ', -1) as Quarter

RESIDENT Temp;

DROP TABLE Temp;

Re: Crosstable with multiple Attribute/Data fields

Fri, 19 Feb 2016 14:24:37 GMT

Thanks for your answer, it works. One more followup, can you fix a Chart on a category? As in have two charts, one always shows revenue, one always showing profits. Or do I have to do it like the other User suggested and split them into one more column?

Re: Crosstable with multiple Attribute/Data fields

Fri, 19 Feb 2016 14:28:58 GMT

Thanks it works like this, wish I could mark multiple as correct. Is there an alternative to KeepChar here? Cause I have a couple more categories that would contain some of the kept characters.

Thanks again!

Re: Crosstable with multiple Attribute/Data fields

sunny_talwar — Fri, 19 Feb 2016 16:55:11 GMT

You can try TextBetween() function:

TextBetween(Quarter, ' ', '|')) as Quarter

Re: Crosstable with multiple Attribute/Data fields

Gysbert_Wassenaar — Fri, 19 Feb 2016 16:55:42 GMT

You can 'fix' the category in the expression. Change for example sum(Value) to sum({<Category={'Profit'}>}Value)