topic Re: Ignore aggr in dimension in QlikView

Ignore aggr in dimension

Anonymous — Tue, 01 Nov 2016 09:15:55 GMT

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!

Re: Ignore aggr in dimension

MK_QSL — Tue, 01 Nov 2016 09:44:37 GMT

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

MK_QSL — Tue, 01 Nov 2016 09:52:23 GMT

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

Re: Ignore aggr in dimension

Anonymous — Mon, 07 Nov 2016 11:59:29 GMT

Thanks)!