Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Prezados, boa tarde!
Preciso carregar uma tabela, porém tenho ela no formato abaixo:
Mas preciso carregar ela no formato abaixo:
Podem me orientar como posso fazer isso?
Wellington
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
(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;
Check this: The Crosstable Load
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;
Hi Sunny,
Your example worked perfectly. But when I try to load another table with some other fields, I get this error bellow:
Would you know where is my mistake?
Wellington
Can you share the script you are using?
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
(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;
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
(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;
Great Sunny. Thank you.
No problem at all
I am glad I was able to help.
Best,
Sunny