topic Re: Merge/consolidate two date fields into one in QlikView

Merge/consolidate two date fields into one

Anonymous — Wed, 03 Sep 2014 14:32:44 GMT

Dear Community,

how can i merge two date fields of one table into one date field.

In Business Objects this is called "merge dimensions".

I read following questions but no one helped me

how to merge dimensions from different sources in qlikview

Re: Merging dates from different columns

Any help welcome

Thanks!

Re: Merge/consolidate two date fields into one

maxgro — Wed, 03 Sep 2014 14:39:28 GMT

in a script?

final:

load date1 as datenew, value from yourtable where len(trim(date1))>0;

concatenate (final) load date2 as date, value from yourtable where len(trim(date2))>0;

Re: Merge/consolidate two date fields into one

Anonymous — Wed, 03 Sep 2014 15:19:00 GMT

Hi Massimo,

thanks for your fast reply!

as i am using SQL i tried it as following:

final:

load *, YEAR1 as datenew;

SQL

Select ...

concatenate (final) load *, YEAR2 as date;

SQL

Select ...

then i get 2 new fields "datenew" and "date", were every one stands just for one date. But i need one common date field. Any ideas?

Re: Merge/consolidate two date fields into one

maxgro — Wed, 03 Sep 2014 15:31:27 GMT

there was an error in my previous post

you should rename the different source date fields with the same name (datenew in below script)

final:

load date1 as datenew, value from yourtable where len(trim(date1))>0;

concatenate (final) load date2 as datenew, value from yourtable where len(trim(date2))>0;

Re: Re: Merge/consolidate two date fields into one

Anonymous — Wed, 03 Sep 2014 15:34:32 GMT

Or as per attached qvw :

Here is its load script :

Temp :

LOAD * INLINE [

Year1, Year2, Value

2011, , 392

2012, , 7164

2013, , 7481

2014, , 75

2014, 2012, 2

2011, 2012, 1

2012, 2011, 2

, 2014, 0

2014, 2013, 9

, 2013, 0

2013, 2014, 22

2014, 2014, 48

, 2011, 0

, 2012, 0

2013, 2012, 89

2011, 2011, 128

2012, 2013, 127

2012, 2012, 4246

2013, 2013, 4816

];

NoConcatenate

Data :

Load

Year1 as Year ,

Value

resident Temp

;

Concatenate (Data)

Load

Year2 as Year ,

Value

;

drop table Temp ;