How to filter a Calculated Colums with a Partition in Datasphere

[hleboeuf]

Hello,

I'm a novice in DS, working for years in Azure and Informatica, so searching my way.

I have a issue in a view where I first create a calculated column like ROW_NUMBER() OVER (PARTITION BY CUSTOMER), its called xROW, until there works fine.

In the next 'block' I want to filter only the 1 values, but there I'm getting the error

The expression is using a calculated column which uses window function.

What is right, but why can't I filter on that column ? I would pfrefer to keep it in a graphical view as on long term we might involve more people and their SQL-coding skills are not that high.

[irodin10]

I think the answers so far are missing the point. Currently Window Functions are not supported in the filter node of a graphical view. That also includes calculating a new column with a window function in the calculation node and then using that column in the filter node. Last time I encountered this I chose to use a SQL View instead, so I'm not sure if a workaround exists within a graphical view.

[Simon_Ye]

you are right. we need to create a calculated column in a node, and set the filter in next node.

[Simon_Ye]

You have two options:

Assuming we want to get the first day of each month from the standard SAP time dimension:

Option 1: FIRST_VALUE function:

FIRST_VALUE(DATE_SAP) OVER (PARTITION BY CALMONTH ORDER BY DATE_SAP)

Option 2: RANK function:

RANK() OVER (PARTITION BY CALMONTH ORDER BY DATE_SAP)

Those two functions just return different values, but you can set the filter on any of them to filter the records, here I only set the filter on "Day Rank in Month" because data preview doesn't support filters like "Date(String)" = "First Day of Month".

[Cocquerel]

instead of (ROW_NUMBER() OVER (PARTITION BY CUSTOMER))=1

you may try CUSTOMER=FIRST_VALUE(CUSTOMER ORDER BY CUSTOMER)