4 Replies Latest reply: Mar 10, 2015 3:53 PM by Massimo Grossi RSS

    Merge fields in one dimension

    Egidio Ottimo

      Good evening everyone,
      i am new of Qlikview and I apologize in advance for my poor English. Now my problem!

      I have a dimension called DescAsaBreve that contains a series of name/text values (ex. x, y, z, u). I would like to create a new value calculated, always in the same dimension DescAsaBreve, i called k, made by the union of two fields (ex. x and y) and then I will sum the relative sales value. I have to use the script with for example concat funciont or a new variable or what? How can I do? Thanks in advance


       

      DescAsaBreve Old

      Sales

      x

      100

      y

      200

      z

      50

      u

      80

       

      DescAsaBreve New

      Sales

      x

      100

      y

      200

      k

      300

      z

      50

      u

      80

        • Re: Merge fields in one dimension

          One alternative would be loading them again with the aggregation applied in the script.

          such as

           

           

          Data:

          LOAD DescAsaBreve,

               Sales

          FROM

          [Book2.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          LOAD 'k' AS DescAsaBreve,

               SUM(Sales) AS Sales

          Resident Data

          Where DescAsaBreve = 'x' OR DescAsaBreve ='y'

          GROUP BY 'k'

          • Re: Merge fields in one dimension
            Massimo Grossi

            Table:

            load * inline [

            DescAsaBreve, Sales

            x,100

            y,200

            z,50

            u,80

            ];

             

            Concatenate (Table)

            load 'k' as DescAsaBreve,

            sum(Sales) as Sales

            Resident Table

            where Match(DescAsaBreve, 'x', 'y');

              • Re: Merge fields in one dimension
                Egidio Ottimo

                Thank you very much for your reply .. actually now I just need to create this new dimension k and no set formulas in the script, which are already provided in the tables in the document.

                I created a new dimension using the script given below, but the formulas in the document are reset as if did not read the new dimension created (but I see in the list box the new dimension created. Where am I wrong?

                Concatenated (Date)
                load '99 - AFH 'as DescASABreve
                Resident Date
                where Match (DescASABreve, '01 - AFH ', '20 - AFH Old', '01 - AFH New ');