Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MrDolph
Contributor
Contributor

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

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
MrDolph_0-1684780312662.png

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

Labels (2)
0 Replies