topic Re: Too many SynKeys through sum() function in QlikView

Too many SynKeys through sum() function

Fri, 07 Aug 2015 09:28:23 GMT

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

Re: Too many SynKeys through sum() function

martinpohl — Fri, 07 Aug 2015 09:33:30 GMT

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 — Fri, 07 Aug 2015 09:34:46 GMT

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 — Fri, 07 Aug 2015 09:35:14 GMT

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 — Fri, 07 Aug 2015 09:38:04 GMT

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 — Fri, 07 Aug 2015 09:42:06 GMT

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

veidlburkhard — Fri, 07 Aug 2015 09:48:09 GMT

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