Merge/consolidate two date fields into one

[Anonymous]

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!

[maxgro]

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;

[Anonymous]

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?

[maxgro · Accepted solution]

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;

[Anonymous]

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 ;