firstValue w/o requiring the ID field in the visual

Question & Answer
1

My dataset consists of sentiment scoring and then sales amounts associated with the customers. However customer to sales records is a 1 to many collection so my sentiment scoring is repeated each time there is more than 1 sale.

I am attempting to use my sentiment id to identify the first instance of a sentiment to only count the score once. I have this calculation working, however, I cannot aggregate this in a table without including the ID in the table, which breaks the purpose for the visual.


firstValue(
    ifelse(contains(Sentiment, 'Very Positive'),${ScoreVeryPositive},
    ifelse(contains(Sentiment, 'Somewhat Positive'),${ScoreSomewhatPositive},
    ifelse(contains(Sentiment, 'Neutral'),${ScoreNeutral},
    ifelse(contains(Sentiment, 'Somewhat Negative'),${ScoreSomewhatNegative},
    ifelse(contains(Sentiment, 'Very Negative'),${ScoreVeryPositive},NULL
    ))))), 
    [{sentiment_external_uid} DESC], 
    [{sentiment_external_uid}]
)

image
In the visual above I don’t want each ID listed, I want to be able to aggregate by other dimensions, like the name of the customer.

firstValue may not be the best approach so I am open to other methods!

Thank you!

2

You will need to use SQL if you do not want to reference the id.

3

Can you try to use the rank function instead? For my use cases, I’ve found that it works better than first value or last value.

Take a look at this:

1 Like
4

Hi Max, do you mean I will need to group it in the SQL used to import the data to SPICE?

5

You will need to translate this

Into SQL. And use that new field in your table.

Then you will have this field and it won’t need to be referenced in the visual.