topic Re: Combining dates from 2 sources in QlikView

Combining dates from 2 sources

sifatnabil — Tue, 09 Dec 2014 06:31:01 GMT

Hi,

I have 2 unrelated tables with 1 common field, Date. However when selecting the dates in the listbox, the dates from the 2 tables are showing up separately, i.e. selecting 03/12/2014 selects the date for table 1 only. There is a duplicate 03/12/2014 linking to the other table. How can I combine the dates such that 1 date selection shows the data from both tables?

Re: Combining dates from 2 sources

PrashantSangle — Tue, 09 Dec 2014 06:35:21 GMT

Hi,

In script use Floor()

try like

Date(Floor(YourDateField)) as NewDate

Regards

Re: Combining dates from 2 sources

its_anandrjs — Tue, 09 Dec 2014 06:37:25 GMT

Hi,

Try to load the tables like below and create common date table

Tab1:

Load * From Source;

Tab2:

Load * From Source;

CommonDate:

Load

Date1,

Date1 as [Date field]

'Table1 Date' as TableFlag

Resident Tab1;

Concatenate(CommonDate)

Load

Date2,

Date2 as [Date field]

'Table2 Date' as TableFlag

Resident Tab2;

MasterCal:

Load

[Date field]

Regards

Anand

Re: Combining dates from 2 sources

mato32188 — Tue, 09 Dec 2014 06:40:47 GMT

Hi Sifat,

make sure your data fields are using same format. You can reach it by date(Field, 'DD/MM/YYYY') as %Date

Martin

Re: Combining dates from 2 sources

sifatnabil — Tue, 09 Dec 2014 08:03:37 GMT

Thanks Anand and max dreamer I've created this Date island using the above and floor(Date), but now the Date format comes out as "DD/MM/YYYY hh:mm:ss" - how can I get rid of the hh:mm:ss when using floor? It didn't remove the timestamp despite using date(floor(DealTime),'DD/MM/YYYY') as DealTime

Re: Combining dates from 2 sources

PrashantSangle — Wed, 10 Dec 2014 04:49:05 GMT

Hi,

use date#() of Subfield() to remove timestamp

try like

Date(Date#(Subfield(DealTime,' '),'DD/MM/YYYY'),'DD/MM/YYYY') as datefield

Note : Use Date#() if needed

Regards