Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Bom dia
preciso agrupar essa tabela por mês podem me ajudar ?
FOR Each vPeriodoBaseCargaIncremental in $(vAnoMesCarregar)
LET vQtdPeriodosCarregar = Round((MonthStart(Today()-1) - Date#(vPeriodoBaseCargaIncremental, 'YYYYMM')) / 30.5) +1;
FOR a=1 to vQtdPeriodosCarregar
IF a > 1 then
LET vPeriodoBaseCargaIncremental = Date(AddMonths(Date#(vPeriodoBaseCargaIncremental, 'YYYYMM'), 1), 'YYYYMM');
ENDIF
Concatenate(Vendas)
CMV:
// first 100000
LOAD
Num#(COD_CLIENTE_SAP,'#########',0) as COD_CLIENTE_SAP,
COD_MATERIAL&'-'&DES_CD as [Key Cod Mat CD],
//COD_MATERIAL,
If(WILDMATCH(COD_TIPO_OFERTA, '*OL*'), 'OL',
If(WILDMATCH(COD_TIPO_OFERTA, '*SC*'), 'CF', 'OFL')) as TIPOVENDA,
date(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'),'DD/MM/YYYY') as Dta,
date(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'),'YYYY-MM') as AnoMês,
Year(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as Ano,
Month(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as Month,
MonthName(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as MonthName,
day(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as DAY,
DES_CD,
VLR_CMV
FROM
[..\5.QVDs\BW_CMV_$(vPeriodoBaseCargaIncremental).QVD](qvd);
NEXT
NEXT
Obrigado.
Guilherme, faça uma nova leitura com resident e utilize o group by conforme o desejado:
Tabela_Tmp:
LOAD
Num#(COD_CLIENTE_SAP,'#########',0) as COD_CLIENTE_SAP,
COD_MATERIAL&'-'&DES_CD as [Key Cod Mat CD],
//COD_MATERIAL,
If(WILDMATCH(COD_TIPO_OFERTA, '*OL*'), 'OL',
If(WILDMATCH(COD_TIPO_OFERTA, '*SC*'), 'CF', 'OFL')) as TIPOVENDA,
date(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'),'DD/MM/YYYY') as Dta,
date(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'),'YYYY-MM') as AnoMês,
Year(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as Ano,
Month(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as Month,
MonthName(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as MonthName,
day(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as DAY,
DES_CD,
VLR_CMV
FROM
[..\5.QVDs\BW_CMV_$(vPeriodoBaseCargaIncremental).QVD](qvd);
Tabela:
LOAD
COD_CLIENTE_SAP,
[Key Cod Mat CD],
TIPOVENDA,
AnoMês,
Ano,
Month,
MonthName,
DES_CD,
SUM(VLR_CMV) as VLR_CMV
RESIDENT Tabela_Tmp
GROUP BY
COD_CLIENTE_SAP,
[Key Cod Mat CD],
TIPOVENDA,
AnoMês,
Ano,
Month,
MonthName,
DES_CD;
DROP TABLE Tabela_Tmp;