topic Several data set with date fields in Connectivity & Data Prep

Several data set with date fields

MaryanVegaC — Thu, 10 Nov 2022 16:50:24 GMT

Hello,

I have been working in a app with several data sets, and the majority of then come with date fields. I have been working with some of then to build my measures, but when the time comes to combine different fields from different data sets and I tried to visualize in a table my measures by a date dimension, some of then don`t work. Someone could advice how to address this issue.

Re: Several data set with date fields

QFabian — Thu, 10 Nov 2022 18:48:01 GMT

Hi @MaryanVegaC , here some tips that i use to deal with dates from different sources and formats.

1.- Check the initial variables in Main tab script, maybe you need 'MM-DD-YYYY' or 'DD/MM/YYYY'. Check against your date fields.

2.- Check every Date field, just puting them as filters on screen :

Example :

As you can see, when the text of the date is at the right side of the filter, it means that is a number, otherwise, qlik couldnt interpretate as date, or as number.

Then you can make the neccesary changes to correct that, in this case, i change some dates by reposisionating parts, or replacing '-' by '/'. There are going to be different many cases in different escenarios, example script:

Date1:
Load * INLINE [
Date1
1/1/2021
];

Date2:
Load * INLINE [
Date2
30/12/2021
];

Date3:
Load * INLINE [
Date3
5-25-2021
];

Dates:
Load
'Date1' as Source,
Date1 as Date
Resident Date1;

Load
'Date2' as Source,
date(makedate(subfield(Date2, '/', 3), subfield(Date2, '/', 2), subfield(Date2, '/', 1))) as Date
Resident Date2;

Load
'Date3' as Source,
replace(Date3, '-', '/') as Date
Resident Date3;

Result :

Re: Several data set with date fields

MaryanVegaC — Thu, 10 Nov 2022 18:58:06 GMT

Thank you. Really good tips!

Re: Several data set with date fields

Vishal_Gupta — Wed, 16 Nov 2022 20:21:30 GMT

@MaryanVegaC in scenario such as this, I feel it's better to go ahead and unify date formats of all date fields.

But before that you should definitely carry the steps mentioned by @QFabian .