8 Replies Latest reply: Jun 24, 2016 3:39 PM by Sunny Talwar RSS

    help with script

    bruno bertels

      Hi

       

      I need help with the creatin of a new dimension depending of certain value in a column :

       

      I have a table and a colum of Rep like that in an excel file :

      In this colum the name is "Name"

      and i have an header by Zone : Zone 1 , Zone 2 , etc with listed under the rep

       

       

      Name
      Zone 1
      rep 1
      rep 2
      rep 3
      rep 4
      rep 5
      rep 6
      rep 7
      rep 8
      Zone 2
      Rep 9
      Rep 10
      Rep 11
      Rep 12
      Rep 13
      Rep 14
      Rep 15
      Rep 16

       

      And i would split this column like that :

       

       

      NameZone
      rep 1Zone 1
      rep 2Zone 1
      rep 3Zone 1
      rep 4Zone 1
      rep 5Zone 1
      rep 6Zone 1
      rep 7Zone 1
      rep 8Zone 1
      Rep 9Zone 2
      Rep 10Zone 2
      Rep 11Zone 2
      Rep 12Zone 2
      Rep 13Zone 2
      Rep 14Zone 2
      Rep 15Zone 2
      Rep 16Zone 2

       

      Can someone help me with the script to add in my script please ?

       

      Thanks

      Bruno

        • Re: help with script
          arul settu

          try like this

           

          name_1:

          load * Inline [

          name

          Zone 1

          rep 1

          rep 2

          rep 3

          rep 4

          rep 5

          rep 6

          rep 7

          rep 8

          Zone 2

          Rep 9

          Rep 10

          Rep 11

          Rep 12

          Rep 13

          Rep 14

          Rep 15

          Rep 16

          ];

           

           

          LOAD name,

          if(WildMatch(name,'*Zone*'),name) as zone,

          if(WildMatch(name,'*rep*'),name) as rep

          Resident name_1;

          • Re: help with script
            Sunny Talwar

            May be this:

             

            Table:

            LOAD *,

              If(WildMatch(Name, 'Zone*'), RangeSum(1, Peek('Key')), Peek('Key')) as Key;

            LOAD * Inline [

            Name

            Zone 1

            rep 1

            rep 2

            rep 3

            rep 4

            rep 5

            rep 6

            rep 7

            rep 8

            Zone 2

            Rep 9

            Rep 10

            Rep 11

            Rep 12

            Rep 13

            Rep 14

            Rep 15

            Rep 16

            ];

             

            FinalTable:

            LOAD Name,

              Key,

              'Zone ' & Key as Zone

            Resident Table

            Where not WildMatch(Name, 'Zone*');

             

            DROP Table Table;

              • Re: help with script
                bruno bertels

                Hi Sunny

                 

                Thanks for your answer. Surely the best way to achieve my requirement , nethertheless i am unable to adapt it to my script....

                 

                first surely due to my poor knoledge in scripting , and second may be because my script is a little bit complicated :

                 

                i have an excel file with several sheet for week and month and quarter

                I use an odbc connection to group each week's sheet together and month's sheet together

                i have no date field in those file

                each sheet is built with the same header column but with multiple header , so that i load column "A" , "B", "C" etc and renamed it

                 

                in each sheet in column A ( named "Conseiller") i have this kind of data :

                A

                ZoneName1

                John

                Paul

                Eric

                Total

                ZoneName2

                Alan

                Debby

                Arthur

                Total

                ZoneName3

                Billy

                Ringo

                Abby

                Lucie

                Total

                TotalFrance

                 

                for instance i exclude all ZoneName and Total and TotalFrance with a where clause <>

                 

                But I realise now i need to be able to group the rep by there own ZoneName.

                I was using another table with name Rep and ZoneName but this table is not up to date so that i missing data with the new rep

                 

                See below my full script part for monthy sheet :

                 

                LIB CONNECT TO '2016 SUIVI CA LINKEO';

                 

                  XlsInfoMois:

                  SQLTables;

                  DISCONNECT;

                  let var=NoOfRows('XlsInfoMois');

                 

                      // Pour chaque feuille du classeur

                      FOR i = 0 to $(var)-1

                 

                      // sheetName = nom de la feuille

                    let sheetName=subfield(peek('TABLE_NAME', i,'XlsInfoMois'),'$',1);

                 

                    // MoisNb = MOIS formatage sheetName au format MOIS

                 

                    let MoisNb=month(date#(sheetName,'MMMM'));

                 

                    // si la MoisNb est supérieur à 0 

                 

                    if(MoisNb)>0 then

                 

                      // alors on charge toute la table et on rajoute une colonne mois avec notre variable issue du nom de la feuille en valeur

                 

                      MyTableMois:

                  

                      LOAD

                //ajout du champ Mois depuis variable et boucle  

                month(date#('$(MoisNb)','MMM')) as "Mois",

                //ajout du champ Trimestre  à partir du champ Mois

                'Tri ' & Num(Ceil(month(date#('$(MoisNb)','MMM'))/3),'(ROM)0') as Trimestre,

                // suppresion des espaces :

                PurgeChar("A",' ') as Conseiller,

                 

                //Acquisition

                "B" AS "Nbre Acquisitions 48 mois",

                "C" AS "Mensualité Acquisitions 48 mois",

                "D" AS "Frais de création Acquisitions 48 mois",

                "E" AS "CA Acquisitions 48 mois",

                "F" AS "Nbre Acquisitions 24 mois",

                "G" AS "Mensualité Acquisitions 24 mois",

                "H" AS "Frais de création Acquisitions 24 mois",

                "I" AS "CA Acquisitions 24 mois",

                "J" AS "Nbre Acquisitions 12 mois",

                "K" AS "Mensualité Acquisitions 12 mois",

                "L" AS "Frais de création Acquisitions 12 mois",

                "M" AS "CA Acquisitions 12 mois",

                 

                 

                If("B">'0','Acq 48',

                If("F">'0','Acq 24',

                If("J">'0','Acq 12'))) as "Acquisition",

                 

                 

                 

                //Migration up &down

                "N" AS "Nbre Migrations 12 en 24",

                "O" AS "Ancienne Mensualité Migrations 12 en 24",

                "P" AS "Nouvelle Mensualité Migrations 12 en 24",

                "Q" AS "frais de création Migrations 12 en 24",

                "R" AS "Nbre Migrations 24 en 12",

                "S" AS "Ancienne Mensualité Migrations 24 en 12",

                "T" AS "Nouvelle Mensualité Migrations 24 en 12",

                "U" AS "frais de création Migrations 24 en 12",

                "V" AS "Nbre Migrations 12 en 48",

                "W" AS "Ancienne Mensualité Migrations 12 en 48",

                "X" AS "Nouvelle Mensualité Migrations 12 en 48",

                "Y" AS "frais de création Migrations 12 en 48",

                "Z" AS "Nbre Migrations 48 en 12",

                "AA" AS "Ancienne Mensualité Migrations 48 en 12",

                "AB" AS "Nouvelle Mensualité Migrations 48 en 12",

                "AC" AS "frais de création Migrations 48 en 12",

                "AD" AS "Nbre Migrations 24 en 48",

                "AE" AS "Ancienne Mensualité Migrations 24 en 48",

                "AF" AS "Nouvelle Mensualité Migrations 24 en 48",

                "AG" AS "frais de création Migrations 24 en 48",

                "AH" AS "Nbre Migrations 48 en 24",

                "AI" AS "Ancienne Mensualité Migrations 48 en 24",

                "AJ" AS "Nouvelle Mensualité Migrations 48 en 24",

                "AK" AS "frais de création Migrations 48 en 24",

                 

                 

                // Dim Migration

                if("N">'0','Mig 12 en 24',

                if("R">'0','Mig 24 en 12',

                if("V">'0','Mig 12 en 48',

                if("Z">'0','Mig 48 en 12',

                if("AD">'0','Mig 24 en 48',

                if("AH">'0','Mig 48 en 24',

                 

                if("AL">'0','Resig 24 en 24',

                if("AP">'0','Resig 48 en 48 (36ème)',

                if("AT">'0','Resig 48 en 48',

                if("AX">'0','Resig 12 en 12',

                if("AP">'0','Vente Addi'))))))))))) as "Migration",

                 

                 

                //Resignature

                "AL" AS "Nbre Resignatures 24 en 24",

                "AM" AS "Ancienne Mensualité Resignatures 24 en 24",

                "AN" AS "Nouvelle Mensualité Resignatures 24 en 24",

                "AO" AS "frais de création Resignatures 24 en 24",

                "AP" AS "Nbre Resignatures 48 en 48 (au 36ème mois)",

                "AQ" AS "Ancienne Mensualité Resignatures 48 en 48 (au 36ème mois)",

                "AR" AS "Nouvelle Mensualité Resignatures 48 en 48 (au 36ème mois)",

                "AS" AS "frais de création Resignatures 48 en 48 (au 36ème mois)",

                "AT" AS "Nbre Resignatures 48 en 48",

                "AU" AS "Ancienne Mensualité Resignatures 48 en 48",

                "AV" AS "Nouvelle Mensualité Resignatures 48 en 48",

                "AW" AS "frais de création Resignatures 48 en 48",

                "AX" AS "Nbre Resignatures 12 en 12",

                "AY" AS "Ancienne Mensualité Resignatures 12 en 12",

                "AZ" AS "Nouvelle Mensualité Resignatures 12 en 12",

                "BA" AS "frais de création Resignatures 12 en 12",

                 

                 

                //Vente Addi

                "BB" AS "Nbre Ventes additionnelles",

                "BC" AS "Augmentation de mensualité Ventes additionnelles",

                "BD" AS "frais de création Ventes additionnelles",

                "BE" AS "CA Ventes additionnelles",

                 

                 

                //Autres

                "BF" AS "Nbre Acquisitions issues Phoning et Parrainages",

                "BG" AS "Nbre Acquisitions total",

                "BH" AS "CA prospect",

                "BI" AS "CA client",

                "BJ" AS "Taux de transfo prospect global",

                "BK" AS "Tx prime acquisition (%)"

                 

                 

                FROM [lib://2016 CA LINKEO/LinkeoSuiviCA.xlsx]

                (ooxml, no labels,header is 2 lines, table is '$(sheetName)')

                 

                where "A" <>'Total'

                and "A"<>'Total France'

                and "A"<>'LinkeoCommerceZone1*'

                and "A"<>'LinkeoCommerceZone2*'

                and "A"<>'LinkeoCommerceZone3*'

                and "A"<>'LinkeoCommerceZone4*'

                and "A"<>'LinkeoCommerceZone5*';

                 

                 

                    END IF

                 

                NEXT i

                DROP Table XlsInfoMois;