6 Replies Latest reply: Aug 7, 2015 5:48 AM by Burkhard Veidl RSS

    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

        • Re: Too many SynKeys through sum() function
          Martin Pohl

          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

            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

              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

                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

                  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

                    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