Counting empty cells and replacing with increasing values

Question & Answer
, ,
21

HHM is not hrs min and I am not extracting hours. I want to replace HGM(int- measurement ) <= 6 consecutive missing values with increasing values ( say 1,2, 3…etc.). Is it possible on quicksight? or I need to consider ETL service?

22

I would recommend doing most of your calculations in SQL if you can, or an ETL service. With these calculations in QuickSight, it can limit how you are able to display this data.

Although, I think it will work in QuickSight.

You might need to do a nested if else like I said before.

Now if there are multiple empty hours in a row you might need to do a nested if else.

ifelse(avg({hour_field})=‘’,ifelse(lag(avg({hour_field}),[{hours_field} ASC],1)=‘’,lag(avg({hour_field}),[{hours_field} ASC],2)+2),lag(avg({hour_field}),[{hours_field} ASC],1)+1),avg({hour_field}))

23

did you try this? i told you {field} = ’ ’ does not work. I am sorry

24

You can replace ‘’ with 0

25

unfortunaltely my data has no missing datetime and other fields your idea is not working. Thanks but it is not the solution

26

@rkhan,

I would request to share a sample dataset of 10-15 rows, if a column includes sensitive data, please replace those values with random values of same data type such as for string column replace with the string values and so on.

Thanks

27

All the string fields are filled with the same integer value except record (I changed it from # to string)

Screenshot_20230117_071631
Screenshot_20230117_0716311308×196 12.8 KB

28

Hi @rkhan ,

I’ve created an example dashboard that may deliver a solution:

image
image1374×618 52.7 KB

The CalcHGM3 calculated field is as follows:

ifelse(
    isNull(avg(HGM3)),
    rank(
        [aTimestamp ASC],
        [avg(HGM3)]
    ),
    avg(HGM3)
)

Could you test this with your data and check if it solves your use-case?

Many Thanks,
Andrew

29

Thank you for the reply!! It some how helps but not 100%. Even though it generates increasing value but I want to increase, for example, if the last value is 4,rows should be filled with 5,6,7,8,9.