Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Ignore aggr in dimension

Good day!

i have one simple source table, which i load to QV

 

Field1Field2Value
Name1Value11
Name1Value21
Name2Value11
Name2Value21
Name2Value31
Name2Value41
Name2Value51
Name2Value61
Name2Value71
Name3Value11
Name3Value21
Name3Value31
Name3Value41

In QV i created pivot with such order of dimensions: Field1, Field2 and there calculate sum(Value).\

Following result:

Field1Field2sum(Value)
Name1Value11
Name1Value21
Name2Value11
Name2Value21
Name2Value31
Name2Value41
Name2Value51
Name2Value61
Name2Value71
Name3Value11
Name3Value21
Name3Value31
Name3Value41

My necessary result - for each Field1 Show all values of Field2 (even if they haven't any value for some Field2 in source table).

Field1Field2sum(Value)
Name1Value11
Name1Value21
Name1Value30
Name1Value40
Name1Value50
Name1Value60
Name1Value70
Name2Value11
Name2Value21
Name2Value31
Name2Value41
Name2Value51
Name2Value61
Name2Value71
Name3Value11
Name3Value21
Name3Value31
Name3Value41
Name3Value50
Name3Value60
Name3Value70

This task i must implement without changing load script and changing order of dimensions.

Very glad for help!

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

If you don't want to change the script.. use as below

Use two Calculated Dimensions

=Aggr(Only({1}Field1),Field1)

=ValueList($(=Chr(39) & concat( Field2, Chr(39) & ',' & chr(39)) & Chr(39)))

Expression

Sum(If(Field2=ValueList($(=Chr(39) & concat( Field2, Chr(39) & ',' & chr(39)) & Chr(39))), Value ))

Now go to Presentation tab of your Pivot Table and untick suppress zero values

View solution in original post

3 Replies
MK_QSL
MVP
MVP

T1:

LOAD Field1,

     Field2,

     Field1 & Field2 as Key,

     Value

FROM

test.xlsx

(ooxml, embedded labels, table is Лист1);

Temp:

Load Distinct Field1 Resident T1;

Join

Load Distinct Field2 Resident T1;

Concatenate(T1)

Load * Resident Temp Where Not Exists(Key, Field1&Field2);

Load Field1, Field2 Resident Temp;

Drop Table Temp;

Drop Field Key;

Use above script..

Now go to Presentation tab of your Pivot Table and untick suppress zero values

MK_QSL
MVP
MVP

If you don't want to change the script.. use as below

Use two Calculated Dimensions

=Aggr(Only({1}Field1),Field1)

=ValueList($(=Chr(39) & concat( Field2, Chr(39) & ',' & chr(39)) & Chr(39)))

Expression

Sum(If(Field2=ValueList($(=Chr(39) & concat( Field2, Chr(39) & ',' & chr(39)) & Chr(39))), Value ))

Now go to Presentation tab of your Pivot Table and untick suppress zero values

Anonymous
Not applicable
Author

Thanks)!