Qlik Community

Qlik Brasil

Group community for Brazil users. discussion only in Portuguese.

Highlighted
Not applicable

Virar tabela ao carregar

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

Tags (1)
1 Solution

Accepted Solutions

Re: Virar tabela ao carregar

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;

8 Replies

Re: Virar tabela ao carregar

Check this: The Crosstable Load

Re: Virar tabela ao carregar

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

Not applicable

Re: Virar tabela ao carregar

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

Can you share the script you are using?

Not applicable

Re: Virar tabela ao carregar

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;

Re: Virar tabela ao carregar

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;

Not applicable

Re: Virar tabela ao carregar

Great Sunny. Thank you.

Re: Virar tabela ao carregar

No problem at all

I am glad I was able to help.

Best,

Sunny