6 Replies Latest reply: Dec 2, 2015 11:54 AM by bruno bertels RSS

    help scripting to handle values in field

    bruno bertels

      Hi Qlickers

       

      I have an excel file like below (and attached)

       

        

      CommercialTaux de transfo
      Zone1
      Nom118,75
      Nom221,74
      Nom318,18
      Nom47,69
      Nom56,9
      Nom63,03
      Nom76,67
      Nom815,62
      Nom93,57
      Nom109,37
      Total9,15
      Zone2
      Nom114,35
      Nom127,89
      Nom1312,5
      Nom144,76
      Nom1511,76
      Nom162,5
      Nom1712,5
      Nom187,14
      Nom193,7
      Nom208,82
      Nom2116,67
      Nom2219,44
      Nom2322,73
      Nom248
      Nom254,76
      Nom263,03
      Nom2712,9
      Total8,16
      Total France8,3

       

       

      In the same field I have lines for commercial , named Zone , and a line" total" for each Zone.

       

      First issue :

      upper I have a line called Zone1 , Zone2 etc I would like to use this values to create a dimension named Zone to get this :

       

      CommercialZoneTaux de transfo
      Zone1
      Nom1Zone118,75
      Nom2Zone121,74
      Nom3Zone118,18
      Nom4Zone17,69
      Nom5Zone16,9
      Nom6Zone13,03
      Nom7Zone16,67
      Nom8Zone115,62
      Nom9Zone13,57
      Nom10Zone19,37
      Total9,15
      Zone2
      Nom11Zone24,35
      Nom12Zone27,89

       

      second Issue :

      I'am trying to pick 'Total' ( as a value in field "Commercial) to use it as the total for a Zone.

      Problem is that there is several lines called Total ( one per Zone).

      And I don't know how to pick each one , renamed it as TotalZone1 , TotalZone2 etc

      ( i can't use a formula to calculate the total per zone because values are % and will give wrong results.

       

      at the end I would like something like that :

       

         

      CommercialZoneTotal for ZoneTaux de transfo
      Zone1
      Nom1Zone19,1518,75
      Nom2Zone19,1521,74
      Nom3Zone19,1518,18
      Nom4Zone19,157,69
      Nom5Zone19,156,9
      Nom6Zone19,153,03
      Nom7Zone19,156,67
      Nom8Zone19,1515,62
      Nom9Zone19,153,57
      Nom10Zone19,159,37
      Total9,15

       

      And drop the line Zone 1 and Total from the column Commercial

       

      Help will be very appreciate

       

      Regards

       

      Bruno

        • Re: help scripting to handle values in field
          Eduardo Sommer

          Hi, you can use the Previous() function, when a Total record is beind handled.

           

          Like

           

          Load Commercial,

                  if (Commercial = 'Total', Previous(Zone), Zone) as Zone,

                  etc

           

          In this case, when you are handling a Total record, the load will use the Zone from the previous record.

           

          Eduardo

          • Re: help scripting to handle values in field
            Sunny Talwar

            May be this:

             

            Table:

            LOAD Commercial,

                [Taux de transfo],

                If(Commercial = 'Total', RangeSum(Peek('Key'), 1), RangeSum(Peek('Key'), 0)) as Key

            FROM

            testDimension.xls

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

             

            Temp1:

            LOAD Commercial,

              [Taux de transfo]

            Resident Table

            Where Commercial Like 'Nom*' and Key = 0;

             

            Join (Temp1)

            LOAD Commercial as Zone

            Resident Table

            Where Commercial Like 'Zone*' and Key = 0;

             

            Join(Temp1)

            LOAD [Taux de transfo] as [Total for Zone]

            Resident Table

            Where Commercial Like 'Total' and Key = 1;

             

            Temp2:

            LOAD Commercial,

              [Taux de transfo]

            Resident Table

            Where Commercial Like 'Nom*' and Key = 1;

             

            Join (Temp2)

            LOAD Commercial as Zone

            Resident Table

            Where Commercial Like 'Zone*' and Key = 1;

             

            Join(Temp2)

            LOAD [Taux de transfo] as [Total for Zone]

            Resident Table

            Where Commercial Like 'Total' and Key = 2;

             

            FinalTable:

            NoConcatenate

            LOAD *

            Resident Temp1;

             

            Concatenate (FinalTable)

            LOAD *

            Resident Temp2;

             

            DROP Tables Table, Temp1, Temp2;


            Capture.PNG

              • Re: help scripting to handle values in field
                Sunny Talwar

                A more dynamic script for more than 2 Zones:

                 

                Table:

                LOAD Commercial,

                    [Taux de transfo],

                    If(Commercial = 'Total', RangeSum(Peek('Key'), 1), RangeSum(Peek('Key'), 0)) as Key

                FROM

                testDimension.xls

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

                 

                Temp:

                LOAD Max(Key) as MaxKey

                Resident Table;

                 

                LET vMaxKey = Peek('MaxKey') - 1;

                DROP Table Temp;

                 

                FOR i = 0 to $(vMaxKey)

                 

                  Temp$(i):

                  LOAD Commercial,

                  [Taux de transfo]

                  Resident Table

                  Where Commercial Like 'Nom*' and Key = $(i);

                 

                  Join (Temp$(i))

                  LOAD Commercial as Zone

                  Resident Table

                  Where Commercial Like 'Zone*' and Key = $(i);

                 

                  Join(Temp$(i))

                  LOAD [Taux de transfo] as [Total for Zone]

                  Resident Table

                  Where Commercial Like 'Total' and Key = ($(i) + 1);

                 

                NEXT i

                 

                LET vConcat = 'NoConcatenate';

                LET vTable = 'FinalTable:';

                 

                FOR j = 0 to $(vMaxKey)

                 

                  $(vTable)

                  $(vConcat)

                  LOAD *

                  Resident Temp$(j);

                 

                  DROP Table Temp$(j);

                  LET vTable = '';

                  LET vConcat = 'Concatenate (FinalTable)';

                 

                NEXT j

                 

                DROP Table Table;

              • Re: help scripting to handle values in field
                Settu Periyasamy

                Hi,

                One more,

                 

                T:
                LOAD Commercial, 
                     [Taux de transfo]
                FROM
                [QV - Others\QVcomm\testDimension.xls]
                (biff, embedded labels, table is Feuil1$);
                
                
                NoConcatenate
                T1:
                Load *,if(len(trim([Taux de transfo]))>0,Peek('Temp_Field'),Commercial) as Temp_Field
                 Resident T;
                DROP Table T;
                
                
                NoConcatenate
                
                
                T2:
                Load Commercial, 
                     [Taux de transfo],
                     Temp_Field,
                     Temp_Field&Commercial as Key
                Resident T1 Where len(trim([Taux de transfo]))>0;  
                
                
                Left Join(T2)
                
                
                Load Temp_Field,  [Taux de transfo] as Total Resident T2 Where WildMatch(Commercial,'Total'); 
                
                
                DROP Table T1;
                
                
                Final:
                Load Commercial,
                  Temp_Field as Zone,
                  [Taux de transfo],
                  Total as [Total for Zone]
                Resident T2 Where not WildMatch(Commercial,'*Total*');
                DROP Table T2;