Multiple dates in same table ... need an new fields with unique date

[Anonymous]

Hi all,

        I'm attaching my data in excel file. we have 2 date fields from same table ..and i want a unique date field mapping those two date field so that i can use that new field as a dimension in one of my chart

Any suggestion would helps

Sri1

[MK_QSL]

You need to create a Canonical Dates.

Check below link for the post related to query...

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

UPDATE :

Try below...

Temp:

LOAD id,

     date1,

     id2,

     date2

FROM

sample.xlsx

(ooxml, embedded labels, table is Sheet1);

ID1:

Mapping Load

  id,

  date1

Resident Temp;

ID2:

Mapping Load

  id2,

  date2

Resident Temp;

NoConcatenate

Final:

Load

  id,

  ApplyMap('ID1',id) as Date,

  1 as Type

Resident Temp;

Load

  id2,

  ApplyMap('ID2',id2) as Date,

  2 as Type

Resident Temp;

MinMaxDate:

Load

  Date(Min(Date)) as MinDate,

  Date(Max(Date)) as MaxDate

Resident Final;

Let vMinDate = NUM(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(Peek('MaxDate',0,'MaxMaxDate'));

TempCalendar:

Load

  Date($(vMinDate) + RowNo() - 1) as TempDate

AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

MasterCalendar:

Load

  TempDate as Date,

  Month(TempDate) as Month,

  Year(TempDate) as Year

Resident TempCalendar

Order By TempDate Asc;

Drop Tables MinMaxDate, TempCalendar, Temp;

[Gysbert_Wassenaar]

Load the table twice and concatenate the records:

T1:

LOAD id,

     date1,

     id2,

     date2,

    date1 as date

FROM [comm114800.xlsx] (ooxml, embedded labels, table is Sheet1);

Concatenate(T1)

LOAD id,

     date1,

     id2,

     date2,

    date2 as date

FROM [comm114800.xlsx] (ooxml, embedded labels, table is Sheet1);

Keep in mind that your dates are actually timestamps. If you want to use the actual date parts of the timestamp you can use the floor function, e.g. floor(date1) as date.

[its_anandrjs]

Concatenate this table and load single table

Tab1:

LOAD id,

     date1,

     id2,

     date2

FROM

[sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Table1:

LOAD

id as ID,

date1 as Date

Resident Tab1;

Concatenate

Table2:

LOAD

id2 as ID,

date2 as Date

Resident Tab1;

[its_anandrjs]

Concatenate this table and load single table

Tab1:

LOAD id,

     date1,

     id2,

     date2

FROM

[sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Table1:

LOAD

id as ID,

date1 as Date

Resident Tab1;

Concatenate

Table2:

LOAD

id2 as ID,

date2 as Date

Resident Tab1;

[Anonymous]

Thanks for the reply  Gysbert, i tried the above script but the issue is here i getting dupliate values ......

and i cannot use count because many of my id are not uniques

both id and id2 has more like parent child relation.. so with this duplication ..... making my expression as discint count will make calucation miserable

and again thanks for all your efforts

[Anonymous]

Hi Manish ,

                 Thanks for the reply , i just tried your script  and this want my issues is :

i don't want to loose that relation between id and Id2 .

for example for given selection of id = P0533214 i should see id2 = E0545094,D0549935 as shown in side table do you have suggestion for this

and again thanks for all your effort ...it helps me alot

please find the attched sample app

[Anonymous]

Hi thanks for your reply... i'm getting too many duplicates

[its_anandrjs]

But You have duplicate records in the basedata

id	date1	id2	date2
P0533214	6/21/2013	D0549935	12/5/2013
P0533214	6/21/2013	E0545094	10/18/2013
P0532920	6/19/2013	E0532923	6/19/2013
P0533872	6/28/2013	E0533928	6/28/2013

id  P0533214 has two records.

let me know

[Anonymous]

I'm sorry my friend Anand, yes i do have duplicates in my data ..from the script you  provided it just append both my Id1& id2 ad ID which i don't want that to happen as i said earlier i want them as seperate fields  and related to each other, take a look at my attched sample in one of my reply's to Manish.

i'm looking for solution with this output

id	date1	id2	date2	date
P0533214	6/21/2013			6/21/2013
P0533214	6/21/2013			6/21/2013
P0532920	6/19/2013	E0532923	6/19/2013	6/19/2013
P0533872	6/28/2013	E0533928	6/28/2013	6/28/2013
P0533214	6/21/2013	D0549935	12/5/2013	12/5/2013
P0533214	6/21/2013	E0545094	10/18/2013	10/18/2013

hope i'm clear this time

Sri1