Variables obtained from parseDate() and parseInt() shown as null in Analysis

Question & Answer
, , ,
1

I upload the data from RDS and the column is in a string in Json format. I used parseJson() to extract the information and formatted with parseDate() and parseInt() to get the columns in date and int type. The calculation with the obtained date and int is also working properly. However, when I tried to analysis, the columns are shown as null.

image
image968×112 3.58 KB

2

Hi,

Thanks for reaching out. Can you share the exact calculation you used?

Best,
Sean

3

Thanks for the reply. I used parseJson({data},“$.gameStart”) in calculation to extract the date from the original data, and I got a date in string like 2022-07-08T13:59:56. After I used parseDate({start},“yyyy-MM-dd’T’HH:mm:ss”) to transform the variable type from string to date format (shown as 2022-07-08T13:59:56.000Z).

The new date value is good with functions in the date fields, however when I try to publish and visualize, the new date values plus the values obtained from the calculation for these new date values is shown as null.

image
image747×54 2.46 KB

In the analysis table, start and end are the string values obtained from parseJson, gameStart and gameEnd are the date values obtained from the date string, and duration min is the new variables obtained by using function dateDiff among the two date values (which was shown with a specific integer value in the data sets but null in visualization).

Thanks, and look forward to your reply!

4

Hi @Nafis -

This is odd. I was able to reproduce your use case in both SPICE and Direct Query (RedShift) without issue.

  • What is your data source type?
  • Are you using SPICE or Direct Query?
  • Can you provide the JSON you are parsing with parseJson ?

gamestart

parseJson({json},"$.gameStart")

gameStart

parseDate({gamestart},"yyyy-MM-dd'T'HH:mm:ss")

c_duration

datediff(gameStart,gameEnd,"MI")

image
image1039×612 32.8 KB

5

Hi Robdhondt,

Thanks for the reply.
I found the reason. The JSON I used to parse is with “” in both side of the string, but in the database the “” are not shown. After removing “”, the issues are solved.

1 Like