Skip to main content
To homepage To homepage

Hi, I created a field StorageReportIndicator in a table to rank the records in the table. 
This rank is a SQL window function RANK which combines table fields initially loaded from the data source (StorageUnit and ReferenceDateTime below) and lookup fields via table joins (DSA.Datum table, fields WeekKey and DayOfWeek).
 

 

Goal is to have a rank to determine the first record of each week for each storageUnit. The outcome afer execution is that all records have rank 1 which is incorrect. I verified this with the same SQL statement in Management Studio.

Could it be that this window function does not have the right result since the lookup is done after the rank is calculated? Note, the table is not historical and incremental. 
Anyone with experience with this case and is there a proper solution?

Hi @erwin.pas 

It seems that the StorageReportIndicator currently has a bit data type, and as such accepts 0, 1 and NULL as output. Is this intentional?

 


Could it be that this window function does not have the right result since the lookup is done after the rank is calculated?

 

Hi @erwin.pas , the lookup is done first. From a technical perspective that statement is not 100% correct :) but as long as StorageReportIndicator] is just a field with a transformation that is not being used in relations, conditions etc. it should work fine.

As @Christian Hauggaard suggests, I think you should change the datatype. RANK() returns a bigint datatype. If you’re not expecting any big numbers, changing the field to int or smallint will be fine too.

Yes, the issue was the field type being bit instead of smallint. I overlooked that one. Thanks for helping out @Christian Hauggaard  @erik.van.mastrigt !

Reply


Terms and ConditionsCookie settings