Create a common Calendar

[rmahfoudhi]

Hello all,

I want to create a common calendar with a unique date for this case: mention that i hava 3 dates in the model

I have an orders list for each date photo(Historization) and for each datePhoto i have a list of Creation and closure dates (Ps: even the order is closed it still appears in the data set for each DatePhoto) 

I created a Calendar using the Link table and flags but i have something that not working:

the link date(the common date) is associated with multiple Date Photo which is not correct

this is a data sample and a screen shot that shows the problem.

Thank you in advance.

[Kushal_Chawda · Accepted solution]

@rmahfoudhi try below approach.

Data:
LOAD
    "Order",
    DatePhoto,
    "Create Date",
    Floor(DatePhoto)&'|'&Floor("Create Date")&'|'&Floor("Close Date") as Key,
    "Close Date"
FROM [lib://Files/Sample.xlsx]
(ooxml, embedded labels, table is Feuil1);

Link:
Load Key,
     date(SubField(Key,'|',1)) as Date,
     'DatePhoto' as DateFlag;
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

Concatenate
Load Key,
     date(SubField(Key,'|',2)) as Date,
     'Create Date' as DateFlag;
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

Concatenate
Load Key,
     date(SubField(Key,'|',3)) as Date,
     'Close Date' as DateFlag;
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

autonumber Key;

Calendar:
Load *,
     year(Date) as Year,
     month(Date) as Month,
     monthname(Date) as MonthYear;
     
Load date(MinDate+IterNo()-1) as Date
while MinDate+IterNo()-1<=MaxDate;

Load min(Date) as MinDate,
     max(Date) as MaxDate;
     
Load date(FieldValue('Date',RecNo())) as Date
AutoGenerate FieldValueCount('Date');

[rmahfoudhi]

Hello Kushal_Chawda,

Thank you for your response, I think it works i no longer have a DatePhoto associated with many Dates

but can you explain why did you use your Key which combine all the dates and you used also subfield key and you didn't use the column itself like Date_Cloture ou Date_Creation ?

and how you created your Link Table?

[Kushal_Chawda]

@rmahfoudhi  I have used combination of date as key because I wanted to have common date field later using it. Here one order is present in multiple dates so using Order as key in link table would not be good approach. Here Ideas is to just filter out different dates with common date field for which I have key with combined date fields. Subfield is used to extract date part from Key. 1st part of key is DatePhoto, 2nd part Created Date and 3rd Closed Date.