Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

brunobertels
Valued Contributor

help with script

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

Tags (1)
8 Replies
arulsettu
Honored Contributor III

Re: help with script

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;

MVP
MVP

Re: help with script

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;

brunobertels
Valued Contributor

Re: help with script

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;

Not applicable

Re: help with script

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

MVP
MVP

Re: help with script

Are you looping to grab data from different sheet of an Excel file?

brunobertels
Valued Contributor

Re: help with script

Hi Sunny

Yes exactly i'am looping the sheets in an excel file

brunobertels
Valued Contributor

Re: help with script

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

MVP
MVP

Re: help with script

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.

Community Browser