5 Replies Latest reply: Jan 27, 2014 9:40 AM by Nicole Smith RSS

    Switch table structure

      Hello,

       

      I have a table like this one:

      tableauqlik.PNG.png

      And i want to get a table like this:

      Capture.PNG.png

      I want to get a column for each trio Division/Magasin/Hiérarchie produit and only 1 line per day.

       

      How can i do this? Maybe with CrossTable?

       

      Thanks,

      Best Regards,

      Loïc

        • Re: Switch table structure
          Srikanth P

          Hi , You get this with Pivot table on the UI. If you want this in the script level, you need to use generic load or For loop

          • Re: Switch table structure
            Nicole Smith

            Load script like the following should work:

             

            DataTemp:

            LOAD Date, text(Division)&'/'&text(Magasin)&'/'&[Hiérarchie produit] as [Division/Magasin/Hiérarchie produit], [Stock cumulé] INLINE [

                Date, Division, Magasin, Hiérarchie produit, Stock cumulé

                24.01.2014, 0100, 0010, 8101, 0.02

                24.01.2014, 0100, 0901, 8101, 0.388

                24.01.2014, 0100, 0001, 8102, 201.903

                24.01.2014, 0100, 0010, 8102, -101.533

                24.01.2014, 0100, 0901, 8102, 3.804

                24.01.2014, 0100, 0001, 8115, 0.663

                24.01.2014, 0100, 0001, 8117, 252.009

                24.01.2014, 0100, 0010, 8117, -115.254

                24.01.2014, 0100, 0901, 8117, 1.215

                24.01.2014, 0100, 0001, 8119, 19.309

                24.01.2014, 0100, 0010, 8119, -2.827

                24.01.2014, 0100, 0901, 8119, 0.749

                24.01.2014, 0100, 0001, 8133, 134.642

                24.01.2014, 0100, 0010, 8133, -2.084

                24.01.2014, 0100, 0901, 8133, 0.663

                24.01.2014, 0100, 0001, 8155, 97.48

                24.01.2014, 0100, 0010, 8155, 1.03

            ];

             

            Let vNumOfValues = FieldValueCount('Division/Magasin/Hiérarchie produit');

            Set vJoin = ;

             

            FOR i = 1 TO $(vNumOfValues)

                LET vFieldName = '[' & PEEK('Division/Magasin/Hiérarchie produit', $(i)-1, 'DataTemp') & ']';

                LET vFieldValue = chr(39) & PEEK('Division/Magasin/Hiérarchie produit', $(i)-1, 'DataTemp') & chr(39);

              

                Data:

                $(vJoin)

                LOAD Date,

                    [Stock cumulé] as $(vFieldName)

                RESIDENT DataTemp

                WHERE [Division/Magasin/Hiérarchie produit] = $(vFieldValue);

              

                SET vJoin = OUTER JOIN;

                  

            NEXT

             

            DROP TABLE DataTemp;

            Example file also attached.