topic Re: Multiple dates in same table ... need an new fields with unique date in QlikView

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

Anonymous — Thu, 17 Apr 2014 16:52:54 GMT

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

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

MK_QSL — Thu, 17 Apr 2014 17:13:30 GMT

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;

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

Gysbert_Wassenaar — Thu, 17 Apr 2014 17:22:53 GMT

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.

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

its_anandrjs — Thu, 17 Apr 2014 17:33:37 GMT

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;

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

its_anandrjs — Thu, 17 Apr 2014 17:33:38 GMT

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;

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

Anonymous — Thu, 17 Apr 2014 18:22:57 GMT

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

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

Anonymous — Thu, 17 Apr 2014 18:24:26 GMT

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

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

Anonymous — Thu, 17 Apr 2014 18:30:56 GMT

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

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

its_anandrjs — Thu, 17 Apr 2014 18:40:13 GMT

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

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

Anonymous — Thu, 17 Apr 2014 18:48:17 GMT

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

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

its_anandrjs — Thu, 17 Apr 2014 18:53:55 GMT

It is the final output by the load statements

ID	Date
P0532920	6/19/2013
E0532923	6/19/2013
P0533214	6/21/2013
P0533872	6/28/2013
E0533928	6/28/2013
E0545094	10/18/2013
D0549935	12/5/2013

Let me know about this.

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

anbu1984 — Fri, 18 Apr 2014 08:04:56 GMT

Do you need extra rows only for duplicate rows?