Forum Discussion

PeterS's avatar
PeterS
Regular Visitor
3 years ago
Solved

Issue with group calculation / grouping

Dear Community,


I struggle with a code for days now. I have 4 columns in 1 table:

1. TIMESTAMP what is date/time type

2. QUESTIONID what is text type

3. ANSWER what is text type

4. SESSIONID what is text type


I would like to create a new column what contains a value from ANSWER column but only

- where the questionID is "14327",

- and I want to use this ANSWER value to every other row where SESSIONID is the same.

- Also It can happen that for the same sessionID there is more row what contain the mentioned questionid. In this case I want to use the answer where the TIMESTAMP is lower.


I used this code:

SHIFT =
VAR FilteredTable = FILTER(SHIFT_TABLE, [QUESTIONID] = "14327")
VAR GroupedTable =
GROUPBY(
FilteredTable,
[SESSIONID],
"EarliestTime", MINX(CURRENTGROUP(), [TIMESTAMP]),
"AnswerValue", FIRSTNONBLANK(FILTER(CURRENTGROUP(), [TIMESTAMP] = MINX(CURRENTGROUP(), [TIMESTAMP])), [ANSWER])
)
RETURN
LOOKUPVALUE(GroupedTable, [SESSIONID], [SESSIONID], "AnswerValue", [Answer])



 

I got the following error message: "Function LOOKUPVALUE expects a column reference as argument number 1." and also at the last line GroupedTable is underlined and if i move curson uppon it it write "Parameter is not the correct type

I tried many thing and modify the formula but just cannot find a solution.

 

Can anyone help what can be the problem or give suggestion for code modification?

 

Really thanks for help in advance!

Need Help

1 Reply