Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Too many SynKeys through sum() function

Hello guys,

I have following problem:

I need many sum() in my script like:

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;


it looks like this,

But I get allways Synthetic Keys, so: How can I form that for avoiding the synthetic keys?


I tried with

Load *

Resident Sum1;

JOIN

Load *

Resident Sum2;


But it doesnt work

Tags (2)
1 Solution

Accepted Solutions

Re: Too many SynKeys through sum() function

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.

6 Replies
martinpohl
Valued Contributor II

Re: Too many SynKeys through sum() function

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

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

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.


talk is cheap, supply exceeds demand

Re: Too many SynKeys through sum() function

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

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;

veidlburkhard
Contributor III

Re: Too many SynKeys through sum() function

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

Community Browser