4 Replies Latest reply: Mar 4, 2018 5:47 AM by joshi Babu Are RSS

    How to create calculated Dimension in Script level

    joshi Babu Are

      Hi Guru's,

       

      i want to push below calculated Dimension into Script Level.

       

       

      Dual(Country, Aggr(NODISTINCT Sum([Actial Headcount]), [Business Unit], Country))

       

       

      How to achieve This.

       

      Please find the attached Data File,Qvf File.

       

      stalwar1

       

      Thanks,

      Joshi

        • Re: Push calculated Dimension into Script level
          Sebastian Pereira

          Hi Joshi!

           

          You have two ways, depending on your needs.

           

          - Why do you need to push it in script level?

          - Why do you need "DUAL"? In the table, you only use the "Country" value (String side of the Dual)

           

          If you need it for sorting purposes, don't forget you can set the order of each field in "By Expression". So, if your expression Aggr(NODISTINCT Sum([Actual head count]), [Business Unit], Country) is the order:

          - The field should be just Country

          - In Sorting, select these field, and click on "By expression", and in "Expression" box put your one.

           

          If this expression is for order purposes, what is the order do you need?

          • Re: Push calculated Dimension into Script level
            Krishna Nagulapally

            Try Somehting like this -



            Data:

            LOAD

                "Business Unit",

                Country,

                "Location / Cost Center",

                "2016 EOY Headcount",

                "2016 EOY Headcount1",

                "Actual head count"

            FROM [lib://Desktop/Data file.xls]

            (biff, embedded labels, table is Sheet1$);


            left join(Data)

            BU_Sort_Order:

            LOAD * Inline [

            SORT_ORDER,Business Unit

            1,API

            2,DDA

            3,DP

            4,Fine Chemicals

            5,Corporate

            ];


            NoConcatenate

            NewTable:

            Load "Business Unit",

                 Country,

                "Location / Cost Center",

                "2016 EOY Headcount",

                "2016 EOY Headcount1",

                SORT_ORDER,

            Dual(Country, Sum([Actual head count])) as DualledCountry

            resident Data

            group by

                "Business Unit",

                 Country,

                "Location / Cost Center",

                "2016 EOY Headcount",

                "2016 EOY Headcount1",

                SORT_ORDER,

            ;


            Drop table Data;