Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
i have one simple source table, which i load to QV
Field1 | Field2 | Value |
Name1 | Value1 | 1 |
Name1 | Value2 | 1 |
Name2 | Value1 | 1 |
Name2 | Value2 | 1 |
Name2 | Value3 | 1 |
Name2 | Value4 | 1 |
Name2 | Value5 | 1 |
Name2 | Value6 | 1 |
Name2 | Value7 | 1 |
Name3 | Value1 | 1 |
Name3 | Value2 | 1 |
Name3 | Value3 | 1 |
Name3 | Value4 | 1 |
In QV i created pivot with such order of dimensions: Field1, Field2 and there calculate sum(Value).\
Following result:
Field1 | Field2 | sum(Value) |
Name1 | Value1 | 1 |
Name1 | Value2 | 1 |
Name2 | Value1 | 1 |
Name2 | Value2 | 1 |
Name2 | Value3 | 1 |
Name2 | Value4 | 1 |
Name2 | Value5 | 1 |
Name2 | Value6 | 1 |
Name2 | Value7 | 1 |
Name3 | Value1 | 1 |
Name3 | Value2 | 1 |
Name3 | Value3 | 1 |
Name3 | Value4 | 1 |
My necessary result - for each Field1 Show all values of Field2 (even if they haven't any value for some Field2 in source table).
Field1 | Field2 | sum(Value) |
Name1 | Value1 | 1 |
Name1 | Value2 | 1 |
Name1 | Value3 | 0 |
Name1 | Value4 | 0 |
Name1 | Value5 | 0 |
Name1 | Value6 | 0 |
Name1 | Value7 | 0 |
Name2 | Value1 | 1 |
Name2 | Value2 | 1 |
Name2 | Value3 | 1 |
Name2 | Value4 | 1 |
Name2 | Value5 | 1 |
Name2 | Value6 | 1 |
Name2 | Value7 | 1 |
Name3 | Value1 | 1 |
Name3 | Value2 | 1 |
Name3 | Value3 | 1 |
Name3 | Value4 | 1 |
Name3 | Value5 | 0 |
Name3 | Value6 | 0 |
Name3 | Value7 | 0 |
This task i must implement without changing load script and changing order of dimensions.
Very glad for help!
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
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
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
Thanks)!