Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have two tables: Customers and Products. The Customers data is a compilation of transversal cuts, so I have the same client everyday. In the products table, I have historic data from products they have purchased. Both have the client and product with their specific customer id, but only the Customers table has segment type. This segment can vary over time: Premium, Classic, etc.
My inquiry is that I want the Products table, referring to the customer code, to lookup for the LAST segment that the customer was assigned (today). I was thinking about creating a custom table or something related, but I dont know what would be the best approach.
Thanks in advance,
IC
Solved! Go to Solution.
This particular problem is often referred to as a slowly changing dimension. There are several ways to solve this but my preferred method comes from an old friend of mine. check out this link and see if it provides the necessary guidance for you.
https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/
Proud to be a Super User!
Could you please share some sample data and give the expected result?
Regards,
Jimmy Tao
I Have the following table 'Customers':
Codigo_Cliente | Tipo_De_Persona | Codigo_Oficial | Codigo_Ingreso | Month | FechaCarga | Es_Cliente |
442222 | N | LMIESES | Clasico | June | 2019-06-28 | S |
442222 | N | LMIESES | Clasico | May | 2019-05-31 | S |
442222 | N | LMIESES | Preferencial | April | 2019-04-30 | S |
442223 | N | CTEJADA | Clasico | June | 2019-06-28 | S |
442223 | N | CTEJADA | Clasico | May | 2019-05-31 | S |
442223 | N | CTEJADA | Prestige | April | 2019-04-30 | S |
442224 | N | FRSANTANA | Clasico | April | 2019-04-30 | S |
442224 | N | EAVILA | Clasico | May | 2019-05-31 | S |
442224 | N | EAVILA | Preferencial | June | 2019-06-28 | S |
And the following table 'Products':
CODIGO_CLIENTE | NOMBRE_PRODUCTO | NUMERO_PRODUCTO |
442222 | CUENTAS DE AHORROS NÓMINA ELECTRÓNICA DOP | 11305000008088 |
442223 | VISA CLASICA | 668060 |
442224 | CUENTAS DE AHORROS CON LIBRETA USD | 21072030006329 |
442225 | VISA ORO | 498904 |
What I need is to get this:
CODIGO_CLIENTE | NOMBRE_PRODUCTO | NUMERO_PRODUCTO | Segmento |
442222 | CUENTAS DE AHORROS NÓMINA ELECTRÓNICA DOP | 11305000008088 | Clasico |
442223 | VISA CLASICA | 668060 | Clasico |
442224 | CUENTAS DE AHORROS CON LIBRETA USD | 21072030006329 | Clasico |
How? Selecting the last (based on date closest to today) Codigo_Ingreso from the Customers table and doing a lookup on the Products table.
Please help me with this matter.
This particular problem is often referred to as a slowly changing dimension. There are several ways to solve this but my preferred method comes from an old friend of mine. check out this link and see if it provides the necessary guidance for you.
https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/
Proud to be a Super User!
Hi, @KC @v-yuta-msft
I have decided to do the snowflake version of this link:
https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/
My approach would be to filter using MAX date of the report, however, what if a customer stops appearing on the MAX date because he is out of the company? I should have MAX date per customer in order to maintain all the Customer ID. Also, I should use an SQL query to create the table.
Any suggestions?
Thanks for the help but I didnt understand the explanation in the link. I got lost about the surrogate ID. I will keep trying to understand.
User | Count |
---|---|
128 | |
112 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
116 | |
102 | |
71 | |
57 |