Too many SynKeys through sum() function

[#1]

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

[martinpohl]

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.

[MayilVahanan · Accepted solution]

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.

[Gysbert_Wassenaar]

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.

[Kushal_Chawda]

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;

[sunny_talwar]

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]

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