Multiple dates in same table

[neha_shirsath]

Hello Community,

I'm facing issue with multiple dates.

I've one table containing created date, release date and changed on date and need to create calendar(here i'm using created date for calendar).

I'm attaching the sample data in that if i'm selecting created date as 2 then i want to see all the lot released on 2 and changed on 2.

so how we can do this on set analysis/in script?

Thanks in advance.

-Neha

[marcus_sommer]

Take a look here:

Canonical Date

Why You sometimes should Load a Master Table several times‌

- Marcus

[neha_shirsath]

Hi thanks for the reply.

I've already read this post but in my case not useful as I want to give single calendar selection for the user.

that's why i was thinking if I use the set analysis for the same.

Thanks.

[neha_shirsath]

See the attached app.

I used the set analysis in file but when i'm selecting 2 as date in chart i'm getting 1  as date because my calendar is on created date but i want to see selected date .

[MK_QSL]

Try to upload proper sample! Here it will not make sense providing numbers 1,2,3, instead of date in date field.

[marcus_sommer]

A master-calendar on a canonical date is aimed to provide a single calendar for multiple date-fields whereby it didn't exclude further calendars which might be useful for other more particular sights, too.

To implement this isn't easy and needs some requirements, like a logically connection between the dates and a common key. I think if you searched for canonical date within the community you will find various examples for it.

- Marcus

[petter]

I think this small application might help you see how Set Analysis (Set Expression) works when you need to compare several dates. I have used the Input Box to let you play around with the expressions interactively and quick and get an immediate result in four different tables:

The load script - with an added field named UniqueID which is necessary to get "search within set expressions" to work correctly.

[neha_shirsath]

Hi Manish,

Thanks for the reply.

Please find the attached updated excel file.

Basically the number was indicating date month number.

-Neha

[neha_shirsath]

Hi Petter,

Thank you so much for your detail specification.

This should work on selection on date field rather than unique id field.

How should I do that?

My Requirement is like-

When my selection is Month(Created date) i.e suppose Feb

then I want to see all the lot Created in FEB,Released on FEB and changed in FEB like so on.

-Neha

[MK_QSL · Accepted solution]

Data:
Load * Inline
[
lot created released changed_on
Lot1 01/01/2016 02/02/2016 03/03/2016
Lot2 01/01/2016 02/02/2016 03/03/2016
Lot3 01/01/2016 03/03/2016 03/03/2016
Lot4 01/01/2016 03/03/2016 03/03/2016
Lot5 02/02/2016 03/03/2016 03/03/2016
Lot6 02/02/2016 04/04/2016 03/03/2016
Lot7 02/02/2016 05/05/2016 03/03/2016
Lot8 03/03/2016 06/06/2016 04/04/2016
Lot9 03/03/2016 07/07/2016 04/04/2016
Lot10 04/04/2016 07/07/2016 04/04/2016
Lot11 04/04/2016 07/07/2016 05/05/2016
Lot12 04/04/2016 07/07/2016 06/06/2016
Lot13 04/04/2016 08/08/2016 08/08/2016
Lot14 04/04/2016 08/08/2016 09/09/2016
Lot15 04/04/2016 08/08/2016 09/09/2016
] (delimiter is \t);


Map_Cretaed:
Mapping Load lot, created Resident Data;


Map_released:
Mapping Load lot, released Resident Data;


Map_changed:
Mapping Load lot, changed_on Resident Data;


Final:
Load lot, 'created' as Flag, ApplyMap('Map_Cretaed',lot,'unknown') as CommonDate Resident Data;
Load lot, 'released' as Flag, ApplyMap('Map_released',lot,'unknown') as CommonDate Resident Data;
Load lot, 'changed' as Flag, ApplyMap('Map_changed',lot,'unknown') as CommonDate Resident Data;


Drop Table Data;


Calendar:
MasterCalendar: 
Load 
  Date(TempDate) as CommonDate,
  Week(TempDate) as Week,
  Month(TempDate) as Month
;
Load 
  MinDate + IterNo() - 1 as TempDate
While MinDate + IterNo() - 1 <= MaxDate;
Load
  Min(FieldValue('CommonDate',RecNo())) as MinDate,
  Max(FieldValue('CommonDate',RecNo())) as MaxDate
AutoGenerate FieldValueCount('CommonDate');