Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

andreyfcdk91
New Contributor III

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

Re: Ignore aggr in dimension

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

3 Replies

Re: Ignore aggr in dimension

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

Re: Ignore aggr in dimension

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

andreyfcdk91
New Contributor III

Re: Ignore aggr in dimension

Thanks)!

Community Browser