new customer dimentions

[mauvasco62]

hi everybody,

i need your support.

I would like to add a field (or 2 fields) in the customers table indicating the month and year in which he became a customer,

It could be a table like this:

Customer id	year_new	Month_new
125	2022	4
457	2022	3
685	2023	1
543	2022	12

 Obviously, I should read the revenue tables.

Thank a lot for your support

Mauro

[ogster1974 · Accepted solution]

1. Create a mapping table from your revenue table.

Something like

Mapping

CustomerStartMap:

Load

          CustomerId,

          Date(Min([Order Date]),'DD/MM/YYYY') as "Customer Start Date"

Resident Revenue

Group By CustomerId

;

2. Then in your customer table use ApplyMap() to bring in the start date.  If you want the specific year month fields you can build out in a preload.

Customer:

Load // preload after customer start date created

*,

Year([Customer Start Date]) AS year_new,

month([Customer Start Date]) AS month_new,

;

Load // create customer start date

         *,

         ApplyMap('CustomerStartMap', CustomerID) AS "Customer Start Date"

Resident CustomerTemp

;

[mauvasco62]

Great! Thanks for your help

Have a nice day

Mauro