Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
Name | Zone |
rep 1 | Zone 1 |
rep 2 | Zone 1 |
rep 3 | Zone 1 |
rep 4 | Zone 1 |
rep 5 | Zone 1 |
rep 6 | Zone 1 |
rep 7 | Zone 1 |
rep 8 | Zone 1 |
Rep 9 | Zone 2 |
Rep 10 | Zone 2 |
Rep 11 | Zone 2 |
Rep 12 | Zone 2 |
Rep 13 | Zone 2 |
Rep 14 | Zone 2 |
Rep 15 | Zone 2 |
Rep 16 | Zone 2 |
Can someone help me with the script to add in my script please ?
Thanks
Bruno
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;
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;
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;
Hi,
You can use match function to match the values in the field.
Try like this.
if(match(Name,'Zone*'),'Zone','Rep') as NewName
Hope this help
Are you looping to grab data from different sheet of an Excel file?
Hi Sunny
Yes exactly i'am looping the sheets in an excel file
Hi Sunny
Don't know why all the answers are tagged as correct , but it doesn't work : due to the load of all sheet from each month the incrementation does'nt work.
For exemple un january first Zone have the value Zone 1 , and in february this zone get the value Zone 6 rather than Zone 1 , and 11 in march etc.
have you any idee how to fix it ?
Bruno
I don't really understand your script well enough to give any detailed suggestion, but in general may be concatenate all the data pulled from each of the file and once you have everything in one table, perform the peek() analysis.