Is there a way to create row numbers that count only when a value changes ...
Hello,
I was wondering is there a way to create rows numbers that count only when a value changes, i.e. three rows of data, the first two row ids are CD and the third is TAPE, I would want it to restart the row count when the value changes from CD to TAPE.
------------------------------
Justin Marroquin
Financial Analyst
Dssw, LLC
San Antonio TX
(210) 845-6651
------------------------------
Best Answer
-
Hopefully the following makes sense. I am using classic.prn to create this example. I skipped the modeling portion. Once you have the data modeled, I added a couple of metadata columns (ReportPage and ReportLine):I then combined by GroupBy table with the original Classic table using Combine->Join Tables->Lookup:
I then grouped my table (Transform -> Group) on Order number and Media and aggregated on ReportPage and ReportLine (using the minimum):
This will bring back the first row for each value change (Order # and Media).
Add a formula column to calculate the difference between the minimum reportline and the original one and add 1 (so it won't be zero based):
Hopefully this gets you started.
------------------------------
Chris Porthouse
Professional services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 11-13-2020 12:39 PM
From: Justin Marroquin
Subject: Is there a way to create row numbers that count only when a value changes ...Hello,
------------------------------
I was wondering is there a way to create rows numbers that count only when a value changes, i.e. three rows of data, the first two row ids are CD and the third is TAPE, I would want it to restart the row count when the value changes from CD to TAPE.
Justin Marroquin
Financial Analyst
Dssw, LLC
San Antonio TX
(210) 845-6651
------------------------------
0
Altair EmployeeAnswers
-
Hi @Justin Marroquin,
I'm not sure this is possible in the way you're approaching it, but I am confident there may be another way within Monarch to address the your challenge.
can you tell us a little bit more about what your goal is? What will having the row numbers changing dynamically help you accomplish?
------------------------------
Baba Majekodunmi
Solutions Consultant
Altair Engineering Inc.
Troy VA
2405932495
------------------------------
-------------------------------------------
Original Message:
Sent: 11-13-2020 12:39 PM
From: Justin Marroquin
Subject: Is there a way to create row numbers that count only when a value changes ...Hello,
------------------------------
I was wondering is there a way to create rows numbers that count only when a value changes, i.e. three rows of data, the first two row ids are CD and the third is TAPE, I would want it to restart the row count when the value changes from CD to TAPE.
Justin Marroquin
Financial Analyst
Dssw, LLC
San Antonio TX
(210) 845-6651
------------------------------
0 -
Hopefully the following makes sense. I am using classic.prn to create this example. I skipped the modeling portion. Once you have the data modeled, I added a couple of metadata columns (ReportPage and ReportLine):I then combined by GroupBy table with the original Classic table using Combine->Join Tables->Lookup:
I then grouped my table (Transform -> Group) on Order number and Media and aggregated on ReportPage and ReportLine (using the minimum):This will bring back the first row for each value change (Order # and Media).
Add a formula column to calculate the difference between the minimum reportline and the original one and add 1 (so it won't be zero based):
Hopefully this gets you started.
------------------------------
Chris Porthouse
Professional services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 11-13-2020 12:39 PM
From: Justin Marroquin
Subject: Is there a way to create row numbers that count only when a value changes ...Hello,
------------------------------
I was wondering is there a way to create rows numbers that count only when a value changes, i.e. three rows of data, the first two row ids are CD and the third is TAPE, I would want it to restart the row count when the value changes from CD to TAPE.
Justin Marroquin
Financial Analyst
Dssw, LLC
San Antonio TX
(210) 845-6651
------------------------------
0 -
Thank you, I implemented these steps into my solution and works just like I need it to. This tool continues to serve so many purposes.Chris Porthouse said:Hopefully the following makes sense. I am using classic.prn to create this example. I skipped the modeling portion. Once you have the data modeled, I added a couple of metadata columns (ReportPage and ReportLine):
I then combined by GroupBy table with the original Classic table using Combine->Join Tables->Lookup:
I then grouped my table (Transform -> Group) on Order number and Media and aggregated on ReportPage and ReportLine (using the minimum):This will bring back the first row for each value change (Order # and Media).
Add a formula column to calculate the difference between the minimum reportline and the original one and add 1 (so it won't be zero based):
Hopefully this gets you started.
------------------------------
Chris Porthouse
Professional services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 11-13-2020 12:39 PM
From: Justin Marroquin
Subject: Is there a way to create row numbers that count only when a value changes ...Hello,
------------------------------
I was wondering is there a way to create rows numbers that count only when a value changes, i.e. three rows of data, the first two row ids are CD and the third is TAPE, I would want it to restart the row count when the value changes from CD to TAPE.
Justin Marroquin
Financial Analyst
Dssw, LLC
San Antonio TX
(210) 845-6651
------------------------------
------------------------------
Justin Marroquin
Financial Analyst
Dssw, LLC
San Antonio TX
(210) 798-0123 Ext. 1913
------------------------------
-------------------------------------------
Original Message:
Sent: 11-16-2020 09:50 AM
From: Chris Porthouse
Subject: Is there a way to create row numbers that count only when a value changes ...
Hopefully the following makes sense. I am using classic.prn to create this example. I skipped the modeling portion. Once you have the data modeled, I added a couple of metadata columns (ReportPage and ReportLine):I then combined by GroupBy table with the original Classic table using Combine->Join Tables->Lookup:
I then grouped my table (Transform -> Group) on Order number and Media and aggregated on ReportPage and ReportLine (using the minimum):This will bring back the first row for each value change (Order # and Media).
Add a formula column to calculate the difference between the minimum reportline and the original one and add 1 (so it won't be zero based):
Hopefully this gets you started.
------------------------------
Chris Porthouse
Professional services
Altair
------------------------------
Original Message:
Sent: 11-13-2020 12:39 PM
From: Justin Marroquin
Subject: Is there a way to create row numbers that count only when a value changes ...Hello,
------------------------------
I was wondering is there a way to create rows numbers that count only when a value changes, i.e. three rows of data, the first two row ids are CD and the third is TAPE, I would want it to restart the row count when the value changes from CD to TAPE.
Justin Marroquin
Financial Analyst
Dssw, LLC
San Antonio TX
(210) 845-6651
------------------------------
0