Skip to main content
Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Crosstable with multiple Attribute/Data fields

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

idrevenue Q1profit Q1revenue Q2profit Q2
1231232131323213
254353454435343454

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
sunny_talwar

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;


Capture.PNG

Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
Not applicable
Author

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?

Not applicable
Author

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!

sunny_talwar

You can try TextBetween() function:

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand