Boa tarde Maicon,,,, cara, fico muito agradecido em me responder. Sou novo no Qlik Sense e é normal bater cabeça...
Veja meu script...onde estou errando?
| // vdtinicial | | = $(vAno)&'0101'; |
| // vdtfinal | | = $(vAno)&'1010'; |
| _vDataPosicao | = date(today(1),'YYYY-MM-DD'); |
| _vDataAtual | | = RIGHT(today(1),4)& MID(today(1),4,2)&LEFT(TODAY(1),2); |
//vAno=2010;
vAno=YEAR(Today(1));
vdtinicial=$(vAno)&'0101';
vdtfinal =$(vAno)&'1231';
vAnodt =LEFT(vdtinicial,4);
LIB CONNECT TO 'conexao_fgvtn (tifgvtn-hv_ti)';
//********************
// FATURAMENTO
//********************
Faturamento:
LOAD
NOMEREDE&'-'&NOMEGRUPO&'-'&NOMECLI as Chave,
DATAEMIS,
Month(DATAEMIS) as Mes,
Year(DATAEMIS) as Ano,
NF,
CLIENTE AS NFCLIENTE,
RANKCOD,
RANKDES,
NOMECLI,
CIDADE AS NFCIDADE,
ESTADO AS NFESTADO,
PAIS AS NFPAIS,
REDE AS NFREDE,
NOMEREDE,
GRUPO AS NFGRUPO,
NOMEGRUPO,
VENDEDOR AS NFVENDEDOR,
NOMEVEND AS NFNOMEVEND,
PRODUTO AS NFPRODUTO,
NOMEPROD AS NFNOMEPROD,
GRUPOPROD AS NFGRUPOPROD,
LINHAPROD AS NFLINHAPROD,
ORIGEMPROD AS NFORIGPROD,
NFPRAZOMED,
NFTOTAL,
NFCUSTO,
NFQUANT,
NFVALIPI,
NFVALICM,
NFVALCOFINS,
NFVALPIS,
NFPERCOMIS,
CUSMEDIOMES,
CUSSTANDMES,
CUSSTANDPROD,
CUSMEDIOPROD;
SQL SELECT CONVERT(DATE,D2_EMISSAO,103) AS DATAEMIS,
D2_CLIENTE AS CLIENTE,
| F2_FGCDCML AS RANKCOD, |
| F2_FGDECML AS RANKDES, |
A1_NOME AS NOMECLI,
A1_MUN AS CIDADE,
A1_EST AS ESTADO,
A1_PAIS AS PAIS,
A1_REDE AS REDE,
ZR_DESCRIC AS NOMEREDE,
A1_GRPVEN AS GRUPO,
ACY_DESCRI AS NOMEGRUPO,
F2_VEND1 AS VENDEDOR,
A3_NOME AS NOMEVEND,
D2_COD AS PRODUTO,
B1_DESC AS NOMEPROD,
B1_GRUPO AS GRUPOPROD,
BM_TIPGRU AS LINHAPROD,
| B1_ORIGEM AS ORIGEMPROD, |
| E4_YPRAZME AS NFPRAZOMED, |
| D2_TOTAL AS NFTOTAL, |
| D2_CUSTO1 AS NFCUSTO, |
| D2_QUANT AS NFQUANT, |
| D2_VALIPI AS NFVALIPI, |
| D2_VALICM AS NFVALICM, |
| D2_VALIMP5 AS NFVALCOFINS, |
| D2_VALIMP6 AS NFVALPIS, |
| D2_COMIS1 AS NFPERCOMIS, |
| B9_CM1 AS CUSMEDIOMES, |
| B9_CUSTD AS CUSSTANDMES, |
| B1_CUSTD AS CUSSTANDPROD, |
| B2_CM1 AS CUSMEDIOPROD | |
FROM SD2010 AS SD2
INNER JOIN SF2010 AS SF2 ON F2_DOC=D2_DOC AND F2_CLIENTE=D2_CLIENTE AND F2_LOJA=D2_LOJA AND SF2.D_E_L_E_T_<>'*'
| INNER JOIN SF4010 AS SF4 ON SF4.F4_CODIGO=SD2.D2_TES AND SF4.F4_DUPLIC='S' AND SF4.D_E_L_E_T_ <> '*' | | |
| INNER JOIN SE4010 AS SE4 ON SE4.E4_CODIGO=SF2.F2_COND AND SE4.D_E_L_E_T_ <> '*' | | |
INNER JOIN SB1010 AS SB1 ON B1_COD=D2_COD AND SB1.D_E_L_E_T_<>'*'
INNER JOIN SB2010 AS SB2 ON B2_LOCAL='0106' AND B2_COD=D2_COD AND SB2.D_E_L_E_T_<>'*'
INNER JOIN SBM010 AS SBM ON BM_GRUPO=B1_GRUPO AND SBM.D_E_L_E_T_<>'*'
LEFT JOIN SB9010 AS SB9 ON B9_COD=D2_COD AND B9_LOCAL='0106' AND SUBSTRING(B9_DATA,1,6)=SUBSTRING(D2_EMISSAO,1,6) AND SB9.D_E_L_E_T_<>'*'
INNER JOIN SA1010 AS SA1 ON A1_COD=D2_CLIENTE AND A1_LOJA=D2_LOJA AND SA1.D_E_L_E_T_<>'*'
INNER JOIN ACY010 AS ACY ON ACY_GRPVEN=A1_GRPVEN AND ACY.D_E_L_E_T_<>'*'
INNER JOIN SZR010 AS SZR ON ZR_COD=A1_REDE AND SZR.D_E_L_E_T_<>'*'
INNER JOIN SA3010 AS SA3 ON A3_COD=F2_VEND1 AND SA3.D_E_L_E_T_<>'*'
WHERE D2_EMISSAO BETWEEN $(vdtinicial) AND $(vdtfinal)
//WHERE YEAR(D2_EMISSAO) = $(vAno)
AND SD2.D_E_L_E_T_<>'*';
STORE Faturamento INTO [lib://CargaFaturamento (tifgvtn-hv_ti)/FatFgvtn$(vAnodt).qvd];
//********************
// PEDIDOS
//********************
PedFatur:
LOAD
NOMEREDE&'-'&NOMEGRUPO&'-'&NOMECLI as Chave,
DATE(TODAY(1)) AS DATAPOSICAO,
DATAPREV,
PEDEMISSAO,
//C5_FGCDCML AS RANKCOD,
//C5_FGDECML AS RANKDES,
Day(PEDEMISSAO) as Diaped,
Month(PEDEMISSAO) as Mesped,
Year(PEDEMISSAO) as Anoped,
Day(DATAPREV) as Diaprev,
Month(DATAPREV) as Mesprev,
Year(DATAPREV) as Anoprev,
Day(TODAY(1)) as DiaCart,
Month(TODAY(1)) as MesCart,
Year(TODAY(1)) as AnoCart,
CLIENTE AS PDCLIENTE,
NOMECLI,
CIDADE AS PDCIDADE,
ESTADO AS PDESTADO,
PAIS AS PDPAIS,
REDE AS PDREDE,
NOMEREDE,
GRUPO AS PDGRUPO,
NOMEGRUPO,
VENDEDOR AS PDVENDEDOR,
NOMEVEND AS PDNOMEVEND,
PRODUTO AS PDPRODUTO,
NOMEPROD AS PDNOMEPROD,
GRUPOPROD AS PDGRUPOPROD,
LINHAPROD AS PDLINHAPROD,
ORIGEMPROD AS PDORIGPROD,
PEDPRAZOMED,
PEDQUANT;
SQL SELECT C6_ENTREG AS DATAPREV,
C5_EMISSAO AS PEDEMISSAO,
| //C5_FGCDCML AS RANKCOD, |
| //C5_FGDECML AS RANKDES, |
| C6_NUM AS PEDIDO, | |
C6_CLI AS CLIENTE,
A1_NOME AS NOMECLI,
A1_MUN AS CIDADE,
A1_EST AS ESTADO,
A1_PAIS AS PAIS,
A1_REDE AS REDE,
ZR_DESCRIC AS NOMEREDE,
A1_GRPVEN AS GRUPO,
ACY_DESCRI AS NOMEGRUPO,
C5_VEND1 AS VENDEDOR,
A3_NOME AS NOMEVEND,
C6_PRODUTO AS PRODUTO,
B1_DESC AS NOMEPROD,
B1_GRUPO AS GRUPOPROD,
BM_TIPGRU AS LINHAPROD,
| B1_ORIGEM AS ORIGEMPROD, |
| E4_YPRAZME AS PEDPRAZOMED, |
| C6_VALOR AS PEDVALOR, | |
| C6_QTDVEN AS PEDQUANT |
FROM SC6010 AS SC6
INNER JOIN SC5010 AS SC5 ON C5_CLIENT=C6_CLI AND C5_LOJACLI=C6_LOJA AND C5_NUM=C6_NUM AND SC5.D_E_L_E_T_<>'*'
| INNER JOIN SF4010 AS SF4 ON SF4.F4_CODIGO=SC6.C6_TES AND SF4.F4_DUPLIC='S' AND SF4.D_E_L_E_T_ <> '*' | | |
| INNER JOIN SE4010 AS SE4 ON SE4.E4_CODIGO=SC5.C5_CONDPAG AND SE4.D_E_L_E_T_ <> '*' | | |
INNER JOIN SB1010 AS SB1 ON B1_COD=C6_PRODUTO AND SB1.D_E_L_E_T_<>'*'
INNER JOIN SB2010 AS SB2 ON B2_COD=C6_PRODUTO AND B2_LOCAL='0106' AND SB2.D_E_L_E_T_<>'*'
INNER JOIN SBM010 AS SBM ON BM_GRUPO=B1_GRUPO AND SBM.D_E_L_E_T_<>'*'
INNER JOIN SA1010 AS SA1 ON A1_COD=C5_CLIENT AND A1_LOJA=C5_LOJACLI AND SA1.D_E_L_E_T_<>'*'
INNER JOIN ACY010 AS ACY ON ACY_GRPVEN=A1_GRPVEN AND ACY.D_E_L_E_T_<>'*'
INNER JOIN SZR010 AS SZR ON ZR_COD=A1_REDE AND SZR.D_E_L_E_T_<>'*'
INNER JOIN SA3010 AS SA3 ON A3_COD=C5_VEND1 AND SA3.D_E_L_E_T_<>'*'
WHERE C5_EMISSAO BETWEEN '20150101' AND '20161231'
AND C5_LIBERPV='S'
AND C6_BLQ<>'R'
AND C6_QTDVEN>C6_QTDENT
AND SC6.D_E_L_E_T_<>'*';
STORE PedFatur INTO [lib://CargaPedFatur (tifgvtn-hv_ti)/PedFatFgvtn_$(_vDataPosicao).qvd];
Link:
LOAD DISTINCT
NOMEREDE&'-'&NOMEGRUPO&'-'&NOMECLI as Chave,
NOMECLI,
NOMEREDE,
NOMEGRUPO
FROM [lib://CargaFaturamento (tifgvtn-hv_ti)/FatFgvtn$(vAnodt).qvd];
Link:
LOAD DISTINCT
NOMEREDE&'-'&NOMEGRUPO&'-'&NOMECLI as Chave,
NOMECLI,
NOMEREDE,
NOMEGRUPO
FROM [lib://CargaPedFatur (tifgvtn-hv_ti)/PedFatFgvtn_$(_vDataPosicao).qvd];
