8 Replies Latest reply: Jun 6, 2016 8:41 AM by Sunny Talwar RSS

    Virar tabela ao carregar

    Wellington Santos

      Prezados, boa tarde!

       

      Preciso carregar uma tabela, porém tenho ela no formato abaixo:Original.JPG

       

      Mas preciso carregar ela no formato abaixo:

      Desejado.JPG

      Podem me orientar como posso fazer isso?

       

      Wellington

          • Re: Virar tabela ao carregar
            Sunny Talwar

            For your example:

             

            Table:

            CrossTable(MES_ANO, VENDA, 4)

            LOAD CODPRODUTO,

                CODFOR,

                NME_SECUNDARIO_FORNECEDOR,

                UF,

                [04_2016],

                [03_2016],

                [02_2016],

                [01_2016]

            FROM

            Community_219511.xlsx

            (ooxml, embedded labels, table is Sheet1);

             

            FinalTable:

            NoConcatenate

            LOAD CODPRODUTO,

                CODFOR,

                NME_SECUNDARIO_FORNECEDOR,

                UF,

                Date(MonthStart(Date#(MES_ANO, 'MM_YYYY')), 'MM_YYYY') as MES_ANO,

                VENDA

            Resident Table;

             

            DROP Table Table;

             

            Capture.PNG

              • Re: Virar tabela ao carregar
                Wellington Santos

                Hi Sunny,

                 

                Your example worked perfectly. But when I try to load another table with some other fields, I get this error bellow:

                ERROR.JPG

                Would you know where is my mistake?

                 

                Wellington

                  • Re: Virar tabela ao carregar
                    Sunny Talwar

                    Can you share the script you are using?

                      • Re: Virar tabela ao carregar
                        Wellington Santos

                        Yes, this is bellow:

                         

                        Table:

                        CrossTable(MES_ANO, VENDA, 4)

                        LOAD CODPRODUTO,

                             CODFOR,

                             NME_SECUNDARIO_FORNECEDOR,

                             NME_REDUZIDO,

                             NME_PRODUTO,

                             UF,

                             VB_04_2016 as '[04_2016]',

                             VB_03_2016 as '[03_2016]',

                             VB_02_2016 as '[02_2016]',

                             VB_01_2016 as '[01_2016]',

                             VB_12_2015 as '[12_2015]',

                             VB_11_2015 as '[11_2015]',

                             VB_10_2015 as '[10_2015]',

                             VB_09_2015 as '[09_2015]',

                             VB_08_2015 as '[08_2015]',

                             VB_07_2015 as '[07_2015]',

                             VB_06_2015 as '[06_2015]',

                             VB_05_2015 as '[05_2015]',

                             VB_04_2015 as '[04_2015]',

                             VB_03_2015 as '[03_2015]',

                             VB_02_2015 as '[02_2015]',

                             VB_01_2015 as '[01_2015]',

                             VB_12_2014 as '[12_2014]',

                             VB_11_2014 as '[11_2014]',

                             VB_10_2014 as '[10_2014]',

                             VB_09_2014 as '[09_2014]',

                             VB_08_2014 as '[08_2014]',

                             VB_07_2014 as '[07_2014]',

                             VB_06_2014 as '[06_2014]',

                             VB_05_2014 as '[05_2014]',

                             VB_04_2014 as '[04_2014]',

                             VB_03_2014 as '[03_2014]',

                             VB_02_2014 as '[02_2014]',

                             VB_01_2014 as '[01_2014]',

                             VB_12_2013 as '[12_2013]',

                             VB_11_2013 as '[11_2013]',

                             VB_10_2013 as '[10_2013]',

                             VB_09_2013 as '[09_2013]',

                             VB_08_2013 as '[08_2013]',

                             VB_07_2013 as '[07_2013]',

                             VB_06_2013 as '[06_2013]',

                             VB_05_2013 as '[05_2013]',

                             VB_04_2013 as '[04_2013]',

                             VB_03_2013 as '[03_2013]',

                             VB_02_2013 as '[02_2013]',

                             VB_01_2013 as '[01_2013]',

                             VB_12_2012 as '[12_2012]',

                             VB_11_2012 as '[11_2012]',

                             VB_10_2012 as '[10_2012]',

                             VB_09_2012 as '[09_2012]',

                             VB_08_2012 as '[08_2012]',

                             VB_07_2012 as '[07_2012]',

                             VB_06_2012 as '[06_2012]',

                             VB_05_2012 as '[05_2012]',

                             VB_04_2012 as '[04_2012]',

                             VB_03_2012 as '[03_2012]',

                             VB_02_2012 as '[02_2012]',

                             VB_01_2012 as '[01_2012]',

                             VB_12_2011 as '[12_2011]',

                             VB_11_2011 as '[11_2011]',

                             VB_10_2011 as '[10_2011]',

                             VB_09_2011 as '[09_2011]',

                             VB_08_2011 as '[08_2011]',

                             VB_07_2011 as '[07_2011]',

                             VB_06_2011 as '[06_2011]',

                             VB_05_2011 as '[05_2011]'

                        FROM

                        [C:\Users\wellington.santos\Desktop\Nova pasta\vendas_por_uf_vb.csv]

                        (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                         

                         

                         

                        FinalTable:

                        NoConcatenate

                        LOAD CODPRODUTO,

                             CODFOR,

                             NME_SECUNDARIO_FORNECEDOR,

                             NME_REDUZIDO,

                             NME_PRODUTO,

                             UF,

                            Date(MonthStart(Date#(MES_ANO, 'MM_YYYY')), 'MM_YYYY') as MES_ANO,

                            VENDA

                        Resident Table;

                         

                        DROP Table Table;

                          • Re: Virar tabela ao carregar
                            Sunny Talwar

                            I think you are using 6 fields here:

                             

                            Table:

                            CrossTable(MES_ANO, VENDA, 6)

                            LOAD CODPRODUTO,

                                 CODFOR,

                                 NME_SECUNDARIO_FORNECEDOR,

                                 NME_REDUZIDO,

                                 NME_PRODUTO,

                                 UF,

                                 VB_04_2016 as '[04_2016]',

                                 VB_03_2016 as '[03_2016]',

                                 VB_02_2016 as '[02_2016]',

                                 VB_01_2016 as '[01_2016]',

                                 VB_12_2015 as '[12_2015]',

                                 VB_11_2015 as '[11_2015]',

                                 VB_10_2015 as '[10_2015]',

                                 VB_09_2015 as '[09_2015]',

                                 VB_08_2015 as '[08_2015]',

                                 VB_07_2015 as '[07_2015]',

                                 VB_06_2015 as '[06_2015]',

                                 VB_05_2015 as '[05_2015]',

                                 VB_04_2015 as '[04_2015]',

                                 VB_03_2015 as '[03_2015]',

                                 VB_02_2015 as '[02_2015]',

                                 VB_01_2015 as '[01_2015]',

                                 VB_12_2014 as '[12_2014]',

                                 VB_11_2014 as '[11_2014]',

                                 VB_10_2014 as '[10_2014]',

                                 VB_09_2014 as '[09_2014]',

                                 VB_08_2014 as '[08_2014]',

                                 VB_07_2014 as '[07_2014]',

                                 VB_06_2014 as '[06_2014]',

                                 VB_05_2014 as '[05_2014]',

                                 VB_04_2014 as '[04_2014]',

                                 VB_03_2014 as '[03_2014]',

                                 VB_02_2014 as '[02_2014]',

                                 VB_01_2014 as '[01_2014]',

                                 VB_12_2013 as '[12_2013]',

                                 VB_11_2013 as '[11_2013]',

                                 VB_10_2013 as '[10_2013]',

                                 VB_09_2013 as '[09_2013]',

                                 VB_08_2013 as '[08_2013]',

                                 VB_07_2013 as '[07_2013]',

                                 VB_06_2013 as '[06_2013]',

                                 VB_05_2013 as '[05_2013]',

                                 VB_04_2013 as '[04_2013]',

                                 VB_03_2013 as '[03_2013]',

                                 VB_02_2013 as '[02_2013]',

                                 VB_01_2013 as '[01_2013]',

                                 VB_12_2012 as '[12_2012]',

                                 VB_11_2012 as '[11_2012]',

                                 VB_10_2012 as '[10_2012]',

                                 VB_09_2012 as '[09_2012]',

                                 VB_08_2012 as '[08_2012]',

                                 VB_07_2012 as '[07_2012]',

                                 VB_06_2012 as '[06_2012]',

                                 VB_05_2012 as '[05_2012]',

                                 VB_04_2012 as '[04_2012]',

                                 VB_03_2012 as '[03_2012]',

                                 VB_02_2012 as '[02_2012]',

                                 VB_01_2012 as '[01_2012]',

                                 VB_12_2011 as '[12_2011]',

                                 VB_11_2011 as '[11_2011]',

                                 VB_10_2011 as '[10_2011]',

                                 VB_09_2011 as '[09_2011]',

                                 VB_08_2011 as '[08_2011]',

                                 VB_07_2011 as '[07_2011]',

                                 VB_06_2011 as '[06_2011]',

                                 VB_05_2011 as '[05_2011]'

                            FROM

                            [C:\Users\wellington.santos\Desktop\Nova pasta\vendas_por_uf_vb.csv]

                            (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                             

                            FinalTable:

                            NoConcatenate

                            LOAD CODPRODUTO,

                                 CODFOR,

                                 NME_SECUNDARIO_FORNECEDOR,

                                 NME_REDUZIDO,

                                 NME_PRODUTO,

                                 UF,

                                Date(MonthStart(Date#(MES_ANO, 'MM_YYYY')), 'MM_YYYY') as MES_ANO,

                                VENDA

                            Resident Table;

                             

                            DROP Table Table;