LOAD cnes as CNES,
ANO_CMPT as Ano,
MES_CMPT as Mês,
ANO_CMPT&' / '&MES_CMPT as Data,
PROC_REA as PR,
QT_PROC as QTS,
estab_nome as Nome,
estab_uf as UF,
estab_cidade as Cidade,
Month(Date#(MES_CMPT,'MM')) as Meses
FROM
[E:\ciha.qvd]
(qvd);
LET vMin = Num(MakeDate(2015));
LET vMax = Num(MakeDate(2023));
Calendar:
LOAD
Month,
Month(Month),
Year(Month) as Year;
LOAD
Date(MonthStart($(vMin),IterNo()-1),'YYYY / MM') as Month
AutoGenerate 1
While MonthStart($(vMin),IterNo()-2) <= $(vMax);
This table lists CNES, that's an ID for a hospital, it's name (Nome), it's city (Cidade), it's State (UF), various procedures each hospital can have done in a single month (PR), the quantities of each procedure performed (QTS) and the respective dates (Data) reported by each hospital
the only ones truly meaning and that i want to use are the CNES, PR, QTS and DATA:
the sheet has a span of 8 years and 2 months, starting from jan 2015 to feb 2023, i have created a master calendar that encompasses and gives me all the distinct 98 months the original table contains, but not all hospital contain entries for every month, i want to join the master calendar to the table so that every CNES has at least 98 rows, at least one for each month that the table covers
the left column of lists are from the original table, the right column was all made by the master calendar, i want to join them and generate the missing entries for each and every CNES