topic Re: Selecting from straight table in QlikView

Selecting from straight table

Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I am using a straight table and there are 7 dimensions.

let's say the table has column name as project Name

and its values are project 1, project 2 ,....project 7..

I want to have one straight table that only shows project 1,2,3,4

and another straight table that shows 5,6,7

dimension limit won't work because the expression result for each project is not ordered high to low or low to high from project 1 to project 7...

is there any way to seperate this out???

Thank you!

Re: Selecting from straight table

johnw — Mon, 17 Oct 2016 22:31:30 GMT

Maybe this? Calculated dimension in each chart. I believe this assumes your projects are loaded in the order you want to see them. We could probably do something similar with firstsortedvalue() for alphabetical maybe. Not sure what order you want them in in the real world. If I was designing for your specific case, I'd just strip off the last character as my sequence, but I assume that's not what your actual data looks like.

=if(fieldindex('Project Name',[Project Name])<=4,[Project Name])

=if(fieldindex('Project Name',[Project Name])> 4,[Project Name])

Re: Selecting from straight table

Oleg_Troyansky — Mon, 17 Oct 2016 22:34:42 GMT

Hi David,

You can filter your desired projects using Set Analysis. Depending on your needs, you can use either a simple selection of values or an advanced search. If your choice of projects for the 2 straight tables is fixed (always projects 1, 2, 3, 4 in the first table and always 5, 6, 7, 8 in the second table, then you can simply formulate your set analysis with these fixed values.

Otherwise, you can use Advanced Search filter and rank your projects based on a certain metric and then filter your projects based on their Rank - one table always includes ranks 1-4, then ranks 5-8, etc...

cheers,

Oleg Troyansky

Learn Set Analysis and other Qlik Techniques from my new book QlikView Your Business

Re: Selecting from straight table

Anil_Babu_Samineni — Tue, 18 Oct 2016 02:18:27 GMT

Just Take Those Only like below using the Calc Dimension

Straight Table1:

If(WildMatch([Project Name],'Project 1','Project 2','Project 3', 'Project 4'),[Project Name])

Same as another table

Straight Table2:

If(WildMatch([Project Name],'Project 5','Project 6','Project 3'),[Project Name])

Re: Selecting from straight table

Tue, 18 Oct 2016 21:48:09 GMT

Hi Guys! I think it will be so much better If I can provide you an example.

Let's say Alpha, Beta, Gamma belongs to CoreGroup

and delta, eplison, zeta, NULL belongs to SubGroup.

what I basically want to do is provide a breakdown of counts of each group. I want something that looks like this from the table.

CoreGroup	count(UniqueID)
Alpha	7
Beta	8
Gamma	9
SubGroup
delta	9
epsilon	14
zeta	17
NULL	11

Group	UniqueID
Alpha	a00
Beta	a01
Gamma	a02
delta	a03
epsilon	a04
zeta	a05
Alpha	a06
Beta	a07
Gamma	a08
delta	a09
epsilon	a10
zeta	a11
Alpha	a12
Beta	a13
Gamma	a14
delta	a15
epsilon	a16
zeta	a17
Beta	a18
Gamma	a19
delta	a20
epsilon	a21
zeta	a22
Alpha	a23
Beta	a24
Gamma	a25
delta	a26
epsilon	a27
zeta	a28
epsilon	a29
zeta	a30
Alpha	a31
Beta	a32
Gamma	a33
delta	a34
epsilon	a35
zeta	a36
zeta	a37
epsilon	a38
zeta	a39
Beta	a40
Gamma	a41
delta	a42
epsilon	a43
zeta	a44
epsilon	a45
zeta	a46
Beta	a47
zeta	a48
Alpha	a49
Beta	a50
Gamma	a51
delta	a52
epsilon	a53
zeta	a54
zeta	a55
epsilon	a56
zeta	a57
Beta	a58
Gamma	a59
delta	a60
epsilon	a61
epsilon	a62
zeta	a63
Beta	a64
zeta	a65
Alpha	a66
Beta	a67
	a68
	a69
	a70
	a71
	a72
	a73
	a74
	a75
	a76
	a77
	a78

Please Help!!

Thank you!!

Re: Selecting from straight table

Tue, 18 Oct 2016 21:52:11 GMT

stalwar1‌

Can you help me Sunny ? if you have time?

Re: Selecting from straight table

johnw — Tue, 18 Oct 2016 21:52:54 GMT

I'd add a [Group] field with values 'Core' and 'Sub', and create a pivot table with that as the first dimension. I wouldn't be trying to make two different charts. But if you want two different charts (some users are very insistent that things look just so), now you have a dimension you can use in set analysis to do so.

Re: Selecting from straight table

Tue, 18 Oct 2016 22:04:02 GMT

yeah... I wish I could just add a field but the actual data table is a large table and I am requested to show a breakdown of each group... that's why I am struggling..

could you give me a little more lead on how I can use set analysis to do this??

I appreciate your advice!!!

Re: Selecting from straight table

johnw — Tue, 18 Oct 2016 22:28:42 GMT

Maybe there's a little confusion as a result of me not noticing that your field was already called Group. In that case, I'm suggesting a new field, [Group Group]. Table size is a non-issue. Even if you add a derived field to your main table, QlikView will compress your data fairly efficiently. But I also see no problem with:

[Group Groups]:
LOAD * INLINE [
Group Group,Group
Core,Alpha
Core,Beta
Core,Gamma
Sub,delta
Sub,epsilon
Sub,zeta
Sub,NULL
];

As for "show a breakdown of each group", that's exactly what what I suggested would do, modified to use [Group Group].

Dimension 1 = Group Group
Dimension 2 = Group
Expression = count([UniqueID])

Do indent mode with a pivot table, and it'll look a whole lot like what you said you wanted to see. If you really need two charts.

Dimension = Group
Expression = count({<[Group Group]={'Core'}>} [UniqueID])

And the same for the second chart but with 'Sub'. Or you could handle it with the dimension, I think like this (suppressing nulls for the dimension):

Dimension = if([Group Group]='Core',[Group])
Expression = count([Unique ID])

Re: Selecting from straight table

Tue, 18 Oct 2016 22:56:31 GMT

Hi!!

Thank you it worked!!

sorry about the crappy screenshot. i just cannot disclose the info here.

but I still have one issue.

as I told you there is a NULL value. I did

Sub, NULL

in the inline but still it doesnt look like the Sub is including the NULL.

is there any way we can handle the NULL better?

Let me know if you have any solution!

Thank you so much!!

Re: Selecting from straight table

johnw — Tue, 18 Oct 2016 23:01:07 GMT

You could either replace null with 'NULL' in script, or I think you could do this in the script:

NULLASVALUE Group;

I've never done it. I'm not sure how the null values would actually look with that approach. I'd probably just do the replace if I could.

Re: Selecting from straight table

Wed, 19 Oct 2016 16:37:47 GMT

Thank you John for your kind explanation and reminding me about the inline functionality