Qlik Community

Qlik Brasil

Group community for Brazil users. discussion only in Portuguese.

Highlighted
guinunes
New Contributor II

Group by

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.

Tags (1)
1 Reply

Re: Group by

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;