Join Master Calendar to existing table and create a distinct items for every date entry

MrDolph — Mon, 22 May 2023 18:32:58 GMT

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

topic Join Master Calendar to existing table and create a distinct items for every date entry in QlikView

Join Master Calendar to existing table and create a distinct items for every date entry