create one date field out of three

[ioannagr]

I have one source table that contains among other fields the fields date, type, id.

So my partner and I decided that this table we should split in three for our app reasons , one for each type.

The tables now look like

Type 1 : id_1, type_1, date_1

Type 2: id_2, type_2, date_2

Type 3: id_3, type_3, date_3

I now want to calculate avg( aggr count of of all ids),  date) but i don't know how to put all these three date fields into one and if that would be best practice.

Please help!

[jwjackso]

We used Henric's Canonical Date to handle a table with multiple date types.  This allowed us to avoid synthetic keys.

[ioannagr]

@jwjackso  thank you, so much, i will try it and let you know how it went! 🤞

[ioannagr]

Also, @jwjackso , after I have created my only data field,

is it correct to count all ids like so 

(count([ID_1])+count([ID_2])+count([ID_3]))   and do 

avg(aggr((count([ID_1])+count([ID_2])+count([ID_3])) , datefield))?

The date bridge table will contain a key field that could be linked to either one of them, or all of them, or combinations of them.

[jwjackso]

If you consider each ID and Date combination unique, I think your avg would be

(count([ID_1])+count([ID_2])+count([ID_3])) / (count(distinct date_1)+count(distinct date_2)+count(distinct date_3))

[ioannagr]

no, the dates are not unique, 

each id can have multiple dates for each type.

I was asking mostly of the count part, is it okay to write it like so?

i just want to count all of them, as i would do count(id) in the source table before splitting it into three.

And with the kpi i want to calculate the avg ids per date. @jwjackso 

[jwjackso]

Can you provide a sample of the original data and how you split the data.

[ioannagr]

Hi @jwjackso , let me update you. I think that canonical date is not what i am looking for.

I had one source table like that: id, type, date 

and for one id i could have different rows of types and dates like that:

id type date

1  1         1/1/2020

1 1           2/1/2020

1 2          1/1/2020

1  3        2/1/2020

2  1      14/3/2020

3   2       5/5/2020

3   1       4/5/2020

now for each  type (1, 2, or 3) we split this table into 3 and dropped the main one, so the qvds look like 

Table_Type 1 :

id_1, type_1, date_1

1  1         1/1/2020

1 1           2/1/2020

2  1      14/3/2020

3   1       4/5/2020

Table_Type 2: id_2, type_2, date_2

Table_Type 3: id_3, type_3, date_3

As you see, one id can have multiple date_1, date_2 and date_3.

How do I make a field out of them to use it for filters and  axis dimensions?

Thanks in advance

[jwjackso]

You need to create a master calendar.

tempMasterCalendar:

load date_1 as [Date]

resident [Table_Type 1];

Concatenate

load date_2 as [Date]

resident [Table_Type 2];

concatenate

load date_3 as [Date]

resident [Table_Type 3];

MasterCalendar:

load Distinct  [Date],

          [Date] as date_1,

         [Date] as date_2,

         [Date] as date_3

resident tempMasterCalendar;

drop table tempMasterCalendar;

Use the field [Date] in filters and axis dimensions.  The date_1, date_2 and date_3 will be linked to [Date].

[ioannagr]

Thanks, but i see it's creating loops (circular references)! Do you know how to solve it?