Solved: Re: Virar tabela ao carregar - Qlik Community

Report Inappropriate Content · ‎2016-06-03

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

sunny_talwar · ‎2016-06-06

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;

View solution in original post

sunny_talwar · ‎2016-06-03

Check this: The Crosstable Load

sunny_talwar · ‎2016-06-03

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;

Report Inappropriate Content · ‎2016-06-03

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

sunny_talwar · ‎2016-06-03

Can you share the script you are using?

Report Inappropriate Content · ‎2016-06-06

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;

sunny_talwar · ‎2016-06-06

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;

Report Inappropriate Content · ‎2016-06-06

Great Sunny. Thank you.

sunny_talwar · ‎2016-06-06

No problem at all

I am glad I was able to help.

Best,

Sunny