
Re: Too many SynKeys through sum() function
Martin Pohl Aug 7, 2015 5:33 AM (in response to Tobias Meyer)Hi,
try
Facts:
noconcatenate load * resident Sum1;
concatenate (Facts) load * resident Sum2;
and so on.
At the end drop all Sum tables >
drop tables Sum1, Sum2;
Bytheway:
Load *
Resident Sum1;
won't work. Qlik is concatenate tables within the same field structure.

Re: Too many SynKeys through sum() function
mayilvahanan ramasamy Aug 7, 2015 5:34 AM (in response to Tobias Meyer)Hi
Try like this,'
In Fact table, create 2 key like
DocNo&'_'& Dim1 As %DocNoDim1Key,
DocNo&'_'& Dim1&'_'&Dim2 As %DocNoDim1Dim2Key
Then, use %DocNoDim1Key for Sum1 and %DocNoDim1Dim2Key for Sum2.

Re: Too many SynKeys through sum() function
Gysbert Wassenaar Aug 7, 2015 5:35 AM (in response to Tobias Meyer)You could concatenate everything to the fact table by adding CONCATENATE (FactTable) above each load.
Or you can create two new fields to create the groups you now have in the where clauses and do the sums in the front end instead of in the script.

Re: Too many SynKeys through sum() function
kushal chawda Aug 7, 2015 5:38 AM (in response to Tobias Meyer)FactTable:
DocNo,
Dim1,
Dim2,
Amount,
..
FROM X;
Sum1:
Load DocNo, Dim1,
'All' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where Dim1>=400 AND Dim1<=500 ...
Group By DocNo, Dim1;
Load DocNo, Dim1,
'Direct' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where Dim1>=400 AND Dim1<=450...
Group By DocNo, Dim1;
Load DocNo, Dim1,
'Indirect' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where Dim1>=45 0AND Dim1<=500...
Group By DocNo, Dim1;
Sum2:
Load DocNo, Dim1,Dim2
'DB' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where ( Dim1>=400 AND Dim1<=500 ) AND Match(Dim2,'T1*')
Group By DocNo, Dim1,Dim2;
Load DocNo, Dim1,Dim2
'Mat' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where ( Dim1>=400 AND Dim1<=500 ) AND Match(Dim2,'T110')
Group By DocNo, Dim1,Dim2;
Load DocNo, Dim1,Dim2
'Amount' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where ( Dim1>=400 AND Dim1<=500 ) AND Match(Dim2,'T150')
Group By DocNo, Dim1,Dim2;
Final:
noconcatenate
load *
Resident Sum1;
drop table Sum1;
concatenate(Final)
load *
Resident Sum2;
drop table Sum2;

Re: Too many SynKeys through sum() function
Sunny Talwar Aug 7, 2015 5:42 AM (in response to Tobias Meyer)May be this:
Sum1:
Load DocNo, Dim1,
'All' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where Dim1>=400 AND Dim1<=500 ...
Group By DocNo, Dim1;
Load DocNo, Dim1,
'Direct' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where Dim1>=400 AND Dim1<=450...
Group By DocNo, Dim1;
Load DocNo, Dim1,
'Indirect' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where Dim1>=450 AND Dim1<=500...
Group By DocNo, Dim1;
Sum2:
Load DocNo, Dim1,Dim2
'DB' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where ( Dim1>=400 AND Dim1<=500 ) AND Match(Dim2,'T1*')
Group By DocNo, Dim1,Dim2;
Load DocNo, Dim1,Dim2
'Mat' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where ( Dim1>=400 AND Dim1<=500 ) AND Match(Dim2,'T110')
Group By DocNo, Dim1,Dim2;
Load DocNo, Dim1,Dim2
'Amount' as SalesType,
sum(Amount) as SUM_AMOUNT Resident FactTable Where ( Dim1>=400 AND Dim1<=500 ) AND Match(Dim2,'T150')
Group By DocNo, Dim1,Dim2;
Join(FactTable)
LOAD *
Resident Sum1;
Join(FactTable)
LOAD *
Resident Sum2;
DROP Tables Sum1, Sum2;

Re: Too many SynKeys through sum() function
Burkhard Veidl Aug 7, 2015 5:48 AM (in response to Tobias Meyer)Hi Tobias,
I think there is no need of SalesType 'ALL', cause 'ALL' is the sum of 'Direct' and 'Indirect'.
At last this single table should do it:
LOAD DocNo
DIM1
If(DIM1>= 400 and DIM1 <= 450, 'DIRECT', 'INDIRECT') as SalesType,
Sum(AMOUNT) as SUM_AMOUNT Where DIM1 >= 400 and DIM1 <= 500
Group By DocNo, DIM1;
Happy qliking
Burkhard