3 Replies Latest reply: Jul 17, 2018 6:46 AM by Arvind Patil RSS

    Quarter for synthetic keys.

    xxxxx yyyyy

      Quat2014:

      LOAD

          EmployeeID as EmpID,

          SalesPerson,

          Q1,

          Q2,

          Q3,

          Q4

      FROM [lib://Soure Data/Quota.xls]

      (biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

       

       

      Quat2015:

      LOAD

          EmployeeID as EmpID,

          Q1,

          Q2,

          Q3,

          Q4

      FROM [lib://Soure Data/Quota.xls]

      (biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

       

      How can i find the sales in 2014 Quarte and 2015 Quarter.

        • Re: Quarter for synthetic keys.
          youssef belloum

          Hi,

           

          here it depends on what you need in terms of modelisation.

           

          you can join your tables using the joining field EmpID and renaming the Quarter fields using the Year as a description, like this Q1_2015, Q2_2015 and Q1_2014, Q2_2014

           

          example

           

          Quat2014:

          LOAD

              EmployeeID as EmpID,

              SalesPerson,

              Q1 as ,Q1_2014

              Q2 as Q2_2014,

              Q3 as Q3_2014,

              Q4 as Q4_2014

          FROM [lib://Soure Data/Quota.xls]

          (biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

           

          left join //or any other type of join

           

          Quat2015:

          LOAD

              EmployeeID as EmpID,

              Q1 as Q1_2015,

              Q2 as Q2_2015,

              Q3 as Q3_2015,

              Q4 as Q4_2015

          FROM [lib://Soure Data/Quota.xls]

          (biff, embedded labels, header is 1 lines, table is [Quota 2015$]);


          or you can concatenate if you don't want or can't rename your fields


          example:


          Quat2014:

          LOAD

              EmployeeID as EmpID,

              SalesPerson,

              Q1,

              Q2,

              Q3,

              Q4

          FROM [lib://Soure Data/Quota.xls]

          (biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

           

          concatenate


          Quat2015:

          LOAD

              EmployeeID as EmpID,

              Q1,

              Q2,

              Q3,

              Q4

          FROM [lib://Soure Data/Quota.xls]

          (biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

          • Re: Quarter for synthetic keys.
            Petter Skjolden

            Quat2014 does not have a SalesPerson field. That is why you get a synthetic key. If you can either exclude SalesPerson if that field is not relevant or include SalesPerson in the Quat2015 if it is available in the sheet in the Excel workbook.

             

            You should also do a CrossTable load to unpivot the quarters into a Sales field - that will make analysis and building the dashboard much easier.

             

            I would suggest something like this:

             

            SALES:

            CrossTable( Quarter, Sales, 3) LOAD

                EmployeeID as EmpID,

                SalesPerson,

                2014 AS Year,

                Q1,

                Q2,

                Q3,

                Q4

            FROM [lib://Soure Data/Quota.xls]

            (biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

             

            CrossTable( Quarter, Sales, 3 ) LOAD

                EmployeeID as EmpID,

                SalesPerson,

                 2015 AS Year,

                Q1,

                Q2,

                Q3,

                Q4

            FROM [lib://Soure Data/Quota.xls]

            (biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

             

             

            Since the two tables have exactly the same field Qlik will do an auto concatenation into a single table named SALES.

             

             

            In your app you can now use expressions like these:

             

            Sum( {<Year={2015}>} Sales ) - Sum( {<Year={2014}>} Sales )

             

            or

             

            Sum( {<Year={2015},Quarter={'Q1'}>} Sales ) - Sum( {<Year={2014},Quarter={'Q1'}>} Sales )

            • Re: Quarter for synthetic keys.
              Arvind Patil

              Hi

               

              You can add Dummy filed Year  as mentioned above :

               

              SALES:

              CrossTable( Quarter, Sales, 3) LOAD

                  EmployeeID as EmpID,

                  SalesPerson,

                  2014 AS Year,

                  Q1,

                  Q2,

                  Q3,

                  Q4

              FROM [lib://Soure Data/Quota.xls]

              (biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

               

              CrossTable( Quarter, Sales, 3 ) LOAD

                  EmployeeID as EmpID,

                  SalesPerson,

                   2015 AS Year,

                  Q1,

                  Q2,

                  Q3,

                  Q4

              FROM [lib://Soure Data/Quota.xls]

              (biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

               

              Thanks,

              Arvind Patil