9 Replies Latest reply: Jun 23, 2016 8:41 AM by Clever Anjos RSS

    Dúvidas em abas do excel

      Boa tarde amigos

       

      Eu criei um relatório no qlik, mas o código ficou muito grande, gostaria de saber se tem como refinar esse código e deixar ele menor:

       

      SET ThousandSep='.';

      SET DecimalSep=',';

      SET MoneyThousandSep='.';

      SET MoneyDecimalSep=',';

      SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='DD/MM/YYYY';

      SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

      SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

      SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

       

       

       

       

      Dados:

      LOAD

        Date(Data,'YYYYMM') AS [ID_MesAtual],

        *

      Where IsNum(Data);

      LOAD

        MakeDate(Num(Year),Num(Month)) As Data,

        Num(Year) AS Year,

        Num(Year)-1 AS PreviousYear,

        Num(Month) AS Month,

        Series,

        Index,

        [Index Value],

        [Index_Value_Base]

       

      FROM

      [Brazil - Key Segments Production Tracker.xlsx]

      (ooxml, embedded labels, table is [Textiles Prod], filters(

      Replace (2,top,StrCnd(null))

      ));

       

       

      [AsOfDate]:

      LOAD

        Year(Data) AS Ano,

        Month(Data)                 AS Mês,

        MonthName(Data)           AS [Mês Ano]

        ,Date(Data,'YYYYMM') AS ID_MesAtual

        ,'Corrente' as ID_Tipo_MesAno

        ,1 as Flg_MesAnoCorrente

        ,0 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

      CONCATENATE ([AsOfDate])

      LOAD

        Year(Data) AS Ano,

        Month(Data)                   AS Mês,

        MonthName(Data) AS [Mês Ano]

        ,Date(AddYears(Data,-1),'YYYYMM') AS ID_MesAtual

        ,'Anterior' as ID_Tipo_MesAno

        ,0 as Flg_MesAnoCorrente

        ,1 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

       

       

      Dados:

      LOAD

        Date(Data,'YYYYMM') AS [ID_MesAtual],

        *

      Where IsNum(Data);

      LOAD

        MakeDate(Num(Year),Num(Month)) As Data,

        Num(Year) AS Year,

        Num(Year)-1 AS PreviousYear,

        Num(Month) AS Month,

        Series,

        Index,

        [Index Value],

        [Index_Value_Base]

       

      FROM

      [Brazil - Key Segments Production Tracker.xlsx]

      (ooxml, embedded labels, table is [Clothing & Access. Prod], filters(

      Replace (2,top,StrCnd(null))

      ));

       

       

      [AsOfDate]:

      LOAD

        Year(Data) AS Ano,

        Month(Data)                 AS Mês,

        MonthName(Data)           AS [Mês Ano]

        ,Date(Data,'YYYYMM') AS ID_MesAtual

        ,'Corrente' as ID_Tipo_MesAno

        ,1 as Flg_MesAnoCorrente

        ,0 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

      CONCATENATE ([AsOfDate])

      LOAD

        Year(Data) AS Ano,

        Month(Data)                   AS Mês,

        MonthName(Data) AS [Mês Ano]

        ,Date(AddYears(Data,-1),'YYYYMM') AS ID_MesAtual

        ,'Anterior' as ID_Tipo_MesAno

        ,0 as Flg_MesAnoCorrente

        ,1 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

       

       

      Dados:

      LOAD

        Date(Data,'YYYYMM') AS [ID_MesAtual],

        *

      Where IsNum(Data);

      LOAD

        MakeDate(Num(Year),Num(Month)) As Data,

        Num(Year) AS Year,

        Num(Year)-1 AS PreviousYear,

        Num(Month) AS Month,

        Series,

        Index,

        [Index Value],

        [Index_Value_Base]

       

      FROM

      [Brazil - Key Segments Production Tracker.xlsx]

      (ooxml, embedded labels, table is [Footwear & Leather Goods Prod], filters(

      Replace (2,top,StrCnd(null))

      ));

       

       

      [AsOfDate]:

      LOAD

        Year(Data) AS Ano,

        Month(Data)                 AS Mês,

        MonthName(Data)           AS [Mês Ano]

        ,Date(Data,'YYYYMM') AS ID_MesAtual

        ,'Corrente' as ID_Tipo_MesAno

        ,1 as Flg_MesAnoCorrente

        ,0 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

      CONCATENATE ([AsOfDate])

      LOAD

        Year(Data) AS Ano,

        Month(Data)                   AS Mês,

        MonthName(Data) AS [Mês Ano]

        ,Date(AddYears(Data,-1),'YYYYMM') AS ID_MesAtual

        ,'Anterior' as ID_Tipo_MesAno

        ,0 as Flg_MesAnoCorrente

        ,1 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

       

       

      Dados:

      LOAD

        Date(Data,'YYYYMM') AS [ID_MesAtual],

        *

      Where IsNum(Data);

      LOAD

        MakeDate(Num(Year),Num(Month)) As Data,

        Num(Year) AS Year,

        Num(Year)-1 AS PreviousYear,

        Num(Month) AS Month,

        Series,

        Index,

        [Index Value],

        [Index_Value_Base]

       

      FROM

      [Brazil - Key Segments Production Tracker.xlsx]

      (ooxml, embedded labels, table is [Furniture Prod], filters(

      Replace (2,top,StrCnd(null))

      ));

       

       

      [AsOfDate]:

      LOAD

        Year(Data) AS Ano,

        Month(Data)                 AS Mês,

        MonthName(Data)           AS [Mês Ano]

        ,Date(Data,'YYYYMM') AS ID_MesAtual

        ,'Corrente' as ID_Tipo_MesAno

        ,1 as Flg_MesAnoCorrente

        ,0 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

      CONCATENATE ([AsOfDate])

      LOAD

        Year(Data) AS Ano,

        Month(Data)                   AS Mês,

        MonthName(Data) AS [Mês Ano]

        ,Date(AddYears(Data,-1),'YYYYMM') AS ID_MesAtual

        ,'Anterior' as ID_Tipo_MesAno

        ,0 as Flg_MesAnoCorrente

        ,1 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

       

       

      Dados:

      LOAD

        Date(Data,'YYYYMM') AS [ID_MesAtual],

        *

      Where IsNum(Data);

      LOAD

        MakeDate(Num(Year),Num(Month)) As Data,

        Num(Year) AS Year,

        Num(Year)-1 AS PreviousYear,

        Num(Month) AS Month,

        Series,

        Index,

        [Index Value],

        [Index_Value_Base]

       

      FROM

      [Brazil - Key Segments Production Tracker.xlsx]

      (ooxml, embedded labels, table is [Automobiles Prod], filters(

      Replace (2,top,StrCnd(null))

      ));

       

       

      [AsOfDate]:

      LOAD

        Year(Data) AS Ano,

        Month(Data)                 AS Mês,

        MonthName(Data)           AS [Mês Ano]

        ,Date(Data,'YYYYMM') AS ID_MesAtual

        ,'Corrente' as ID_Tipo_MesAno

        ,1 as Flg_MesAnoCorrente

        ,0 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

      CONCATENATE ([AsOfDate])

      LOAD

        Year(Data) AS Ano,

        Month(Data)                   AS Mês,

        MonthName(Data) AS [Mês Ano]

        ,Date(AddYears(Data,-1),'YYYYMM') AS ID_MesAtual

        ,'Anterior' as ID_Tipo_MesAno

        ,0 as Flg_MesAnoCorrente

        ,1 as Flg_MesAnoAnterior

      ;

      LOAD DISTINCT Data

      RESIDENT Dados

      ;

        • Re: Dúvidas em abas do excel
          Yuri Nicolett

          Thiago, para lhe ajudarmos melhor: o que você esta querendo fazer com os dados?

          Seu código basicamente cria tabelas de datas de comparação, mas você explicando melhor o que precisa fazer podemos até melhorar o seu código.

            • Re: Dúvidas em abas do excel

              Yuri, é isso mesmo é uma tabela de comparação de dados, mas eu dupliquei o código para cada aba da planilha, queria saber se tem como eu fazer o código pegar as abas [Automobiles Prod], [Furniture Prod], etc... sem ter que fazer o mesmo código pra cada uma delas.

              • Re: Dúvidas em abas do excel

                Yuri, eu consigo fazer se as abas tiverem o mesmo nome(plan1), faço um for i = 1 to 5 e altero table is [Automobiles Prod] para plan$(i)

                 

                mas como as abas estão com nomes diferentes esse modo não da certo

                Eu pensei em criar variáveis plan1, 2, etc... para cada aba da planilha e depois usar esse modo que citei acima, mas também não funcionou.

                 

                Você tem alguma ideia de como fazer isso?

                  • Re: Dúvidas em abas do excel
                    Alessandro Furtado

                    Thiago, boa tarde.

                    No post eu coloquei um modelo que le os nomes das abas. O codigo é do  rwunderlich  (Rob Wunderlich)

                     

                    Segue o codigo

                     

                     

                     

                    /* Connect using the Jet provider. Specify the workbook path in the "Data Source" spec below. */

                    CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=workbook.xls;Extended Properties="Excel 8.0;"];

                     

                    /*

                    The SQLtables statement will load the "tables" table with a set of fields

                    describing the tables in the ODBC datasource. For Excel, each sheet is a table.

                    */

                    tables:  // Name the table that will be created by SQLTables stmt

                    SQLtables;

                    DISCONNECT; // Don't need ODBC connection anymore

                     

                     

                    /*

                    One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.

                    We will loop through this set of sheet names.

                    */

                    FOR i = 0 to NoOfRows('tables')-1

                      /*

                      Assign the TABLE_NAME to the variable "sheetName".

                      TABLE_NAMEs that contain spaces will be enclosed in single quotes.

                      The purgeChar function will remove any quotes.

                      */

                      LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

                      // Optional filtering logic to select certain sheets

                      IF wildmatch('$(sheetName)', 'Sales*') THEN  // Only sheetNames that begin "Sales"

                      Sales:   

                      // Now that we have a sheet name, a standard biff (Excel) load can be used.

                      LOAD *,

                      '$(sheetName)' as Sheet  // Optionally, the sheetName value may be loaded as a field

                      FROM workbook.xls (biff, embedded labels, table is [$(sheetName)]);

                      END IF // End of optional sheet filtering

                    NEXT

                     

                     

                    DROP TABLE tables;   // The table list is no longer needed

                    • Re: Dúvidas em abas do excel
                      Yuri Nicolett

                      O exemplo o Alessandro colocou vai funcionar, pois é realizada uma conexão via ODBC na planilha excel e através dela encontramos todas as abas do excel, independente de qual nome essas abas tenham.

                  • Re: Dúvidas em abas do excel
                    Clever Anjos

                    Já conseguiu a resposta? Se sim, escolha uma e marque como "Correta" para fechar o tópico e nos ajudar a manter a comunidade organizada