topic Re: Adding a Date Column to existing table to create one master to many dates in App Development

Adding a Date Column to existing table to create one master to many dates

AndyC — Tue, 14 May 2024 13:40:12 GMT

I am trying to generate a matrix table with two columns:

Partcode , Date

I have the table of partcodes and I want to add a series of dates to each partcode record between a mix and max date.

So I start with a table containing just partcode,

etc..

and end up with two columns...

A, 2024-04-01 - this will be a Date() field but wanted to show the date so it doesn't confuse Americans 😉

A, 2024-04-02

A, 2024-04-03

B, 2024-04-01

B, 2024-04-02

B, 2024-04-03

C, 2024-04-01

C, 2024-04-02

C, 2024-04-03

etc..

I know how to generate a table of dates but I just can't work out how to join the two!

MasterCalendar:

LOAD TempDate As CalendarDate;

LOAD Date($(vStartDate) + IterNo()) AS TempDate

AutoGenerate 1

While $(vStartDate) + IterNo() <= $(vEndDate);

Partcodes:

LOAD

Distinct

PART_NO

Resident SalesData;

So, essentially read through the Partcodes table and for each row add all the rows from the master record.

Anyone got an idea?

Re: Adding a Date Column to existing table to create one master to many dates

hic — Tue, 14 May 2024 13:49:21 GMT

Just write "Join" between the two tables:

MasterCalendar:

LOAD TempDate As CalendarDate;

LOAD Date($(vStartDate) + IterNo()) AS TempDate

AutoGenerate 1

While $(vStartDate) + IterNo() <= $(vEndDate);

Join

LOAD

Distinct

PART_NO

Resident SalesData;

Re: Adding a Date Column to existing table to create one master to many dates

AndyC — Tue, 14 May 2024 14:00:52 GMT

So obvious now you've mentioned it!

😁 'Embarrassed Emoji'

Thanks for helping