Generating SortColumn
-
Hi,
I have problems with generating a sort column which I can use in Power BI:
As you can see in the attached files I have a category column which all have Unique Dates.
My goal is to create some kind of Row_Number columns which is Sorted on Category and ordered by DATE.
So basically what I need is a column that have the same ID for each category but is still sorted by the date column.
I have tried with nested Dense_Rank columns but without success.
-
Will row_number() serve your purpose?
-
Hi Mark,
Not really. As you can see in the attached Screen dump the Row_Number will index the same categories but it should be the opposite.
-
You want to generate a single data item which (somehow) combines both Category and Date, is that correct?
What form should this composite value take?
Something like
XXnnnnnn,
where XX is a code for the category and nnnnnn is a numeric sequence, in date order, for the dates within that category?
If not, please describe the format you want the output to take.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I'm confused. It thought "BI" tools, like the all powerful, wonderous, "can't-live-without-it" Power BI, were supposed to make such things easy without having to write SQL for them.
Also, it's obvious you have some data handy or you wouldn't have been able to attach graphics of it. If you'd take a few minutes to turn it into some "Readily Consumable" data in the form of CREATE TABLE and INSERT (VALUES) along with what the desired result would be, that would help your cause on this thread.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Guru,
I need to have the same granularity in SortColumn as in Category.
So somehow sort the sort column by date within the same categories
The reason for this is that I need to use the column in my frontend tool (Power BI) to sort the category column by date.
- Jeff Moden wrote:
I'm confused. It thought "BI" tools, like the all powerful, wonderous, "can't-live-without-it" Power BI, were supposed to make such things easy without having to write SQL for them.
Also, it's obvious you have some data handy or you wouldn't have been able to attach graphics of it. If you'd take a few minutes to turn it into some "Readily Consumable" data in the form of CREATE TABLE and INSERT (VALUES) along with what the desired result would be, that would help your cause on this thread.
Never had these sort of problems in BusinessObjects before SAP got their hands on it and priced it through the roof. Solid semantic layer over a well-designed data warehouse and it was end to end, source-to-report happiness. Power BI is trying to be too many things without mastering any of them imho.
A slight off topic but I just wanted to agree with you about Power BI being over-rated.
-
Hi Jeff,
My required output would look like this
So within each category the sort dense rank is sorted by Date. But the sorting needs to be done already in the Dense_Rank By category column so I have the same granularity as Category. A need to correspond to 1 and B need to Correspond to 2 and so on.
-
Something like this?
DROP TABLE IF EXISTS #SomeData;
CREATE TABLE #SomeData
(
Category VARCHAR(50)
,SomeDate DATETIME
);
INSERT #SomeData
(
Category
,SomeDate
)
VALUES
('A', '20171231')
,('A', '20180101')
,('B', '20180101')
,('B', '20180101');
WITH cats
AS (SELECT DISTINCT
sd.Category
,CatNo = DENSE_RANK() OVER (ORDER BY sd.Category)
FROM #SomeData sd)
SELECT sd.Category
,sd.SomeDate
,SortCol = CONCAT(c.CatNo, '.', ROW_NUMBER() OVER (PARTITION BY sd.Category ORDER BY sd.SomeDate))
FROM #SomeData sd
JOIN cats c
ON c.Category = sd.Category;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi Phil,
Thx for the provided solution.
It works as expected but in order to work this part need to be hidden in the output "CONCAT(t1.TestSort, '.'"
So basically we need this sortorder but without the .xxx part
- siboska wrote:
Hi Jeff,
My required output would look like this
So within each category the sort dense rank is sorted by Date. But the sorting needs to be done already in the Dense_Rank By category column so I have the same granularity as Category. A need to correspond to 1 and B need to Correspond to 2 and so on.
Again, though... there's no readily consumable test data. Just a graphic. 😉 See the article at the first link in my signature line below. It'll help us help you better and faster in the future.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - siboska wrote:
Hi Phil,
So basically we need this sortorder but without the .xxx part
What is the .xxx part? do you mean it needs to be an integer?
Does this work using Phil's test data?
DROP TABLE IF EXISTS #SomeData;
CREATE TABLE #SomeData
( Category VARCHAR(50),
SomeDate DATETIME
);
INSERT #SomeData VALUES ('A', '2017-12-31'),('A', '2018-01-01'),
('B', '2018-01-01'), ('B', '2018-01-01');
SELECT *,
DENSE_RANK() OVER (ORDER BY Category) * 100 +
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY SomeDate) AS SortOrder
FROM #SomeData
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply