Data structure in excel files/ ODBC/ multiple sheets load/

[brunobertels]

Hi community,

Hope someone could help me as i'am facing a very difficult challenge for me.

I'am essentialy a Qliksense desktop user , not an expert , especially regarding script.

I post a sample of my excel file.

In this example there is multiple sheets : week sheets (eg : S40 ) Month sheets : (eg : september , october) there is also trimester sheets ( T3 , T4) but i dont need them.

Each sheets get the same structure :

first : the dimension's names are "Nbr, Mensualité", "Frais de Création", "CA" and so on and group by Acquisition 48 mois ans so on ...

2nd : the first column is the name of representatives group by CommerceZone1 , CommerceZone2 etc ....

What i would like to do in my data structure is something like that :

So

I need a ODBC connection to get the excel data file.

I need to loop the sheet to separate week sheets from month sheet and I need to use the sheet name, week or month as dimension to add in the data model.

I need to add the Zonecommerce name as a dimension also.

I need to use the third line in the excel sheet as dimension name and group them by categories ( Acquisition 24 , acquisition 48 etc)

There is in my excel file a "Total" row under each Zonecommerce that i don't need to import.

I know how to do that all but separetely and not in the same full script.

Could some one help me on this

Regards and thanks in advance

Bruno

[marcus_sommer]

Your needed sheets could you get per odbc-query, see: Loading from multiple Excel files and multiple sheets and I would suggest to use The Crosstable Load to transform your data to "normal" data-rows and by multiplen headern is this useful: Re: CrossTable from Excel with Multiple Headers.

Very helpful is LOAD data into QlikView where you by 1.2 finds explanation how to use a where-clause to exclude certain records.

- Marcus

[brunobertels]

Hi Marcus

thanks a lot for your response and advise.

In fact i'am able to create an ODBC connection ( it's take me about 4 months to understand how to get it) I'm a full newbie

I'am able to understand the sript to loop excell sheet.

I've already made a crosstable statement in another app and i know how to use where clause.

Unfortunatelly i really don't know how to manage all those pieces of script.

If someone could help it will be great.

nethertheless i will have an attentive glance to your links this week end.

Bruno

[brunobertels · Accepted solution]

Hi Marcus ,

At the end .... it's working with this

LIB CONNECT TO 'APP_ODBC';

  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,

And all my fields i need

FROM [lib://monfichier.xlsx]

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

    END IF

NEXT i

DROP Table XlsInfoMois;

Bruno