topic Re: How to union 3 dates in two tables for use in filter in App Development

How to union 3 dates in two tables for use in filter

FredSantos — Fri, 15 Jul 2022 12:02:18 GMT

I have 2 tables, table one have 2 dates (CreatedDate and TransitionDate), table have one date(PaymentDate).I have one key for connecting tables. I need to union for 3 dates in one Date for use filter Date in project. Example:

Table1:
LOAD * INLINE [
Key, CreatedDate, TransitionDate
01, 01/01/2014, 10/02/2014
02, 05/01/2014,
03, 05/01/2014,
04, 05/01/2014, 10/01/2014
05, 06/01/2014, 10/01/2014
06, 06/01/2014,
07, 06/01/2014,
08, 01/02/2014, 05/02/2014
09, 01/02/2014, 10/02/2014
10, 01/02/2014, 18/02/2014
11, 05/02/2014,
12, 05/02/2014,
13, 10/02/2014, 01/03/2014
14, 01/03/2014, 10/03/2014
];

Table2:
LOAD * INLINE [
Key, Value, PaymentDate
01, 10,
02, 2, 07/01/2014
03, 1, 08/01/2014
04, 5,
05, 11,
06, 8, 17/01/2014
07, 12, 06/01/2014
08, 4,
09, 8,
10, 4,
11, 3, 27/02/2014
12, 5, 01/03/2014
13, 10,
14, 2,
];

Re: How to union 3 dates in two tables for use in filter

justISO — Fri, 15 Jul 2022 12:39:39 GMT

Hi, not so elegant solution, but maybe you can try something like this:

Date_temp: Load distinct Key, CreatedDate as CreatedDateX, CreatedDate as Date RESIDENT Table1 WHERE len(CreatedDate)>0 and not isnull(CreatedDate); join (Date_temp) Load distinct Key, TransitionDate as TransitionDateX, TransitionDate as Date RESIDENT Table1 WHERE len(TransitionDate)>0 and not isnull(TransitionDate); join (Date_temp) Load distinct Key, PaymentDate as PaymentDateX, PaymentDate as Date RESIDENT Table2 WHERE len(PaymentDate)>0 and not isnull(PaymentDate);

Re: How to union 3 dates in two tables for use in filter

FredSantos — Fri, 15 Jul 2022 13:12:36 GMT

Thanks for solution.