3 Replies Latest reply: Nov 25, 2015 11:00 AM by bruno bertels RSS

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

    bruno bertels

      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 :




      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



        • Re: Data structure in excel files/ ODBC/ multiple sheets load/
          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

            • Re: Data structure in excel files/ ODBC/ multiple sheets load/
              bruno bertels

              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.



              • Re: Data structure in excel files/ ODBC/ multiple sheets load/
                bruno bertels

                Hi Marcus ,


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


                LIB CONNECT TO 'APP_ODBC';





                  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





                //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;