2 Replies Latest reply: Feb 15, 2013 6:31 AM by Vikram Tantry RSS

    How to re-organise my data

    sean kaiser
      Hi experts,
      I have a QVD table with the following sample data in:-
      ProdHier1DivisionSourceCubeValue
      LVP10SOURCE1ZSD22150
      LVP20SOURCE1ZSD2250
      SYS10SOURCE2ZSD44200
      LVP99SOURCE2ZSD4450
      SYS20SOURCE1ZSD22100
      LVP10SOURCE2ZSD2250
      Now I know that combinations of the above equal a Product Grouping that our users want to see.  For instance, ProdHier1=LVP, Division=10, Cube=ZSD22 would be ProductGrouping1.  My question is, how do I turn the above data in my QlikView table into a Table Chart which just has the Product Grouping in rows and Value in Columns:-
      ProductValue
      ProductGrouping1200

       

      I cant quite picture the right way to do it?  Map the data in the script as i load it from the QVD?  Or some other variable expression magic which i cant work out!?

       

      Much thanks in advance.

        • Re: How to re-organise my data
          Stefan Wühl

          if your grouping is static, do this in the script, maybe like

           

          LOAD

               PRODHIER1,

               DIVISION,

               SOURCE,

               CUBE,

               VALUE,

               if(PRODHIER = 'LVP' and DIVISION = 10 and CUBE ='ZSD22', 'ProductGrouping1','SomethingElse')

                     as PRODUCT,

               ....

           

          Regards,

          Stefan

          • Re: How to re-organise my data

            Hi ,

            You can achieve this through backend

            temp1:

            Load distinct ProdHeir,Division,Cube,ProductGrouping&' '&rowno() as ProductGroup

            resident (above table mentioned by you )

             

            temp2:

            Load ProdHeir&Division&Cube as PDC,ProductGrouping

            resident temp1;

             

             

            drop table temp1;

             

            temp3:

            Load *,ProdHeir&Division&Cube as PDC

            resident ((above table mentioned by you ));

            drop table ((above table mentioned by you ));

            left join (temp3)

            load * resident temp2

             

             

            Infront end

            then you can create a chart with dimensioin ProductGrouping and expression sum (Value)