topic Re: Combining two date fields in to one field from same table in QlikView

Combining two date fields in to one field from same table

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

Hi,

I have two date fields both are in MM/DD format in one table and i have to merge those two date fields into one field and use the merged field for Slider.

For Example:

One date field have 01/01 to 12/29 and other date field have 01/02 to 12/29.There are some duplicates also.I have to combine these two date fields in to one field from same table.Please let me know if you have any questions.

Regards,

Pinky

Re: Combining two date fields in to one field from same table

Anil_Babu_Samineni — Mon, 22 Jan 2018 14:30:25 GMT

Is this not working?

Date1 & Date2 as DateField

Re: Combining two date fields in to one field from same table

pinky1234 — Mon, 22 Jan 2018 14:59:59 GMT

Thanks for the reply.If i do Date1 & Date2 dates are coming like this 01/0201/03 for each date.Please find the attached screenshot so that you will understand the problem.Please let me know if you have any questions.

Re: Combining two date fields in to one field from same table

Ben_P — Mon, 22 Jan 2018 15:06:51 GMT

Are you saying that every record has two date fields of "[from date] to [to date]" so for each record you have four dates in total and you want to combine them into a single field?

If so, a simple way would be to split each date field into 2 separate fields in the load, so you have 4 fields in total in QlikView - you could then perform calculations on these to get your final result.

Re: Combining two date fields in to one field from same table

Anil_Babu_Samineni — Mon, 22 Jan 2018 15:11:41 GMT

This case, You can load 2 times like

Load Date1 as DateField From T1;

Concatenate

Load Date2 as DateField From T2;

Re: Combining two date fields in to one field from same table

hic — Mon, 22 Jan 2018 15:52:21 GMT

You may need a Canonical Date

HIC

Re: Combining two date fields in to one field from same table

pinky1234 — Mon, 22 Jan 2018 17:30:13 GMT

Thank you for the reply!

Every date has only one date like 01/02 but when i do Date1 & Date2 as anil suggested the dates are coming like attached screenshot. For example if Date1 has 01/02 to 06/25 and Date 2 has 05/02 to 12/30.So i wanted to combine like 01/02 to 12/30.Hope this helps.Let me know if you have any questions

Regards,

Pinky

Re: Combining two date fields in to one field from same table

pinky1234 — Mon, 22 Jan 2018 17:34:22 GMT

Both the Date1 and Date2 fields are in same table.

Re: Combining two date fields in to one field from same table

pinky1234 — Mon, 22 Jan 2018 17:39:35 GMT

Hi Henric,

Thanks for the reply,

How can Canonical date helps this problem.I wanted to merge two date fields in to one field use that field in same axis.

Note:Two date fields are in same table.

Can you please clarify or send us any sample qvw.

Regards,

Pinky

Re: Combining two date fields in to one field from same table

Anil_Babu_Samineni — Mon, 22 Jan 2018 17:50:24 GMT

Why not this?

Load Date1 as DateField From T1;

Concatenate

Load Date2 as DateField From T1;

Re: Combining two date fields in to one field from same table

pinky1234 — Mon, 22 Jan 2018 18:15:23 GMT

Concatenate is working and i can see the date fields in same axis.

But i am going to use the merged field in slider.If i change the dates in the slider the associative data is incorrect.

For example: Date1 & Date2 merged field is NewDateField.

I have two textbox objects used same above fields Date1 & Date2 like Count(Date1) and Count (Date2). If i scroll over the slider, data showing wrong on the text object.

Re: Combining two date fields in to one field from same table

hic — Mon, 22 Jan 2018 19:04:31 GMT

This is exactly what a canonical date does.

The principle is to create a third date - the canonical date - that links to both Date1 and Date2. So if you click on (or use a slider) to select e.g. 12/31, you will see records that have Date1='12/31' and records that have Date2='12/31',

I suggest you post some sample data that pertain to your situation.

HIC

Re: Combining two date fields in to one field from same table

pinky1234 — Mon, 22 Jan 2018 19:52:15 GMT

Hi Henric,

Thanks for the reply! This is exactly what i want. I am attaching the Qvw with sample data.Can you please let me know how can i create the Canonical date? Can you please reply me the Qvw with Canonical Date.

I appreciate your help.Thanks.Please let me know if you have any questions

Regards,

Pinky

Re: Combining two date fields in to one field from same table

pinky1234 — Tue, 23 Jan 2018 13:04:30 GMT

Hi Henric,

Did you get a chance to look in to this issue.If not can you please look in to the issue when you get a time.

Appreciate your response!

Thanks,

Pinky

Re: Combining two date fields in to one field from same table

hic — Tue, 23 Jan 2018 16:00:37 GMT

In its simplest form, you just have a second table. But you can also add a Master Calendar for the Canonical date as a third table.

HIC