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 :

       

      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

        • 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.

               

              Bruno

              • 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';

                 

                  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