Multiple results encountered for the same location in Pivot Table indicated by ****

camdyn
camdyn Member
in Beast Mode

Attached is a sample of my data. I am trying to create a pivot table with the following:

Rows: location, week, date, shift

Columns: count(unique identifier), count(unique identifier) * shift length

In my ETL, I created a formula to handle empty values. It determines whether a shift was 8 hours or 10 hours, based on a schedule ID field from a separate table. Here is the logic:

CASE
WHEN shift length IS NOT NULL THEN shift length
WHEN shift length IS NULL AND Schedule ID LIKE '%-08 %' THEN 8.0
WHEN shift length IS NULL AND Schedule ID LIKE '%-10 %' THEN 10.0
ELSE 10.0
END

When I try to create this pivot table, I get the error in the title. I've noticed that, when I convert my pivot table to a mega table, the issue is that 8 hour shifts and 10 hour shifts are not being grouped together. The error and the **** goes away when I add a column to separate 8 hour and 10 hour shifts, but I want one total given for each shift/day/location. What is wrong with my logic that is preventing this from happening?

Data Sample (1).xlsx

Tagged:

Answers

  • DavidChurchman
    DavidChurchman Coach

    You need to aggregate shift length so the pivot table knows how to combine the cells. Wrap it in either a sum() or avg(), depending on which makes more sense for you for this case

    I think you might need to wrap the 8s and 10s, too

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • camdyn
    camdyn Member
    edited December 18

    @DavidChurchman Thanks for the reply! When you say wrap the 8s and 10s, what do you mean by that? Also, I've tried doing COUNT(unique identifier) * SUM(shift length) in the analyzer and it did not fix my problem, but the ETL editor won't let me wrap my case statement in a SUM(). I'm getting stuck on how/when that aggregation should happen

Categories