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;
