Dates

kev6brown — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have numerous tables with different dates, when i try to make a calendar from them the data is duplicated for every date in the tables. My script looks like this -

DateLink:
Load
NHSNo,
Date([SDT],'dd/MM/yyyy') as DATE,
Month([SDT]) as Month,
'Episode' as ServiceTable,
Year([SDT]) as Year
RESIDENT Episode;

End if

If vIntermediateCareLoadY1 = 'Yes' Then
Concatenate
Load
NHSNo,
Date([IntermediateCare.ContactDATE],'dd/MM/yyyy') as DATE,
Month([IntermediateCare.ContactDATE]) as Month,
'IntermediateCare' as ServiceTable,
Year([IntermediateCare.ContactDATE]) as Year
RESIDENT IntermediateCare;

End if

If vIntermediateCareLoadY1 = 'Yes' Then
Concatenate
Load
NHSNo,
Date([Community_Therapies.ContactDATE],'dd/MM/yyyy') as DATE,
Month([Community_Therapies.ContactDATE]) as Month,
Year([Community_Therapies.ContactDATE]) as Year,
'Community_Therapies' as ServiceTable
RESIDENT Community_Therapies;

End if

If vIntermediateCareLoadY1 = 'Yes' Then
Concatenate
Load
NHSNo,
Date([Community_Nursing.ContactDATE],'dd/MM/yyyy') as DATE,
Month([Community_Nursing.ContactDATE]) as Month,
Year([Community_Nursing.ContactDATE]) as Year,
'Pathology' as ServiceTable
RESIDENT Community_Nursing;

End if

If vSocialCareContractLoadY1 = 'Yes' Then
Concatenate
Load
NHSNo,
Date([Social_Care_Dom_Care.START_DATE],'dd/MM/yyyy') as DATE,
Month([Social_Care_Dom_Care.START_DATE]) as Month,
Year([Social_Care_Dom_Care.START_DATE]) as Year,
'Pathology' as ServiceTable
RESIDENT Social_Care_Dom_Care;

End if

If vSocialCareContractLoadY1 = 'Yes' Then
Concatenate
Load
NHSNo,
Date([Social_Care_Carehome.START_DATE],'dd/MM/yyyy') as DATE,
Month([Social_Care_Carehome.START_DATE]) as Month,
Year([Social_Care_Carehome.START_DATE]) as Year,
'Pathology' as ServiceTable
RESIDENT Social_Care_Carehome;

End if

If vPathologyLoadY1 = 'Yes' Then
Concatenate
Load
NHSNo,
Date(Pathology.SIDate,'dd/MM/yyyy') as DATE,
Month([Pathology.SIDate]) as Month,
Year([Pathology.SIDate]) as Year,
'Pathology' as ServiceTable
RESIDENT Pathology;

End if

If vRadiologyLoadY1 = 'Yes' Then
Concatenate
Load
[NHSNo],
Date([Radiololgy.SIDate],'dd/MM/yyyy') as DATE,
Month([Radiololgy.SIDate]) as Month,
Year([Radiololgy.SIDate]) as Year,
'Radiololgy' as ServiceTable
RESIDENT Radiololgy;

End if

If vTherapiesLoadY1 = 'Yes' Then
Concatenate
Load
[NHSNo],
Date([Therapies.SIDate],'dd/MM/yyyy') as DATE,
Month([Therapies.SIDate]) as Month,
Year([Therapies.SIDate]) as Year,
'Therapies' as ServiceTable
RESIDENT Therapies;

End if

Do i need a further step for my calendar so I'm not getting duplicates?

Kev

Re: Dates

prieper — Wed, 02 Jan 2019 11:02:23 GMT

If the NHSNo-field exist in all tables, there will already be a synthetic key without putting the calendar.
If the tables are similar, it makes sense to concatenate the tables first and then place a calendar on it.

Re: Dates

bgerchikov — Thu, 03 Jan 2019 19:30:07 GMT

Hi Kev,

I see at least two problems:

1. ServiceTable field.

2. Concatenate keyword

Both cause duplicate dates in your DateLink table.

Try this:

Calendar:

Load distinct

DATE,

Month,

Year

Resident

DateLink;

Drop fields Month, Year from DateLink;

topic Dates in QlikView

Dates

Re: Dates

Re: Dates