[Answered] Nitro Query date field

[wboddicker]

Database file has a month field (2,0), a day field(2,0) and a year field(4,0) which I would like to combine into a 8 digit field to compare to the current date in Nitro query. Can someone tell me if this is possible and if so, how to do so?

[Johnny Major]

You can create a calculated column in your Data Source. Here is an example of creating a date from a year, month and day field.

Date Example.png

If you want to create an 8,0 numeric field instead of a date field you could do something like below.

Date Example 2.png

[wboddicker]

Thanks Johnny!

[Zach]

How could I do essentially the opposite of this. I have a year, month, and day field as an (8,0) single string of numbers, so a date of December 15th, 2017 would be displayed in the field as 20171215. Can I use a calculated field to somehow break this down into three separate fields of year(4,0), month(2,0), and day(2,0)?

[sean.lanktree]

Hi Zach -

Do you actually need your data as 3 separate fields or do you just want to display it differently to the user in a grid?

[Zach]

Hi Sean,

I actually only need the month as a separate field in this scenario. But the month is stuck in that 8 integer field at position 5, and 6.

[sean.lanktree]

OK, you can just created another calculated field named "MONTH" and set the calculation as:

SUBSTR(MYFIELD,5,2)

[Zach]

Thanks Sean!

[Zach]

Can you use a simple IF statement in the "Add Calculation" function in Nitro Query, if so what would this look like? I am trying to create a column in a grid that says either Yes or No based off of the value of a field coming from a database file.

Something similar to this for example:

IF F1.X > 100 and F1.X < 110
EVAL column = 'Yes'
ELSE
EVAL column = 'No'
ENDIF

The one caveat to my specific scenario is that my variable field 'X' is classified as a character field but I need to treat it as numeric in this instance.

[sean.lanktree]

Yes, that is definitely doable. I would suggest just using a renderer on your grid column.

var val = parseInt(v);
if val > 100 && val < 110;
    return 'Yes';
}
return 'No';

[sean.lanktree]

Sorry Zach, just realized I was not clear at all. I sent you code for the "renderer" on a grid column. I would suggest simply omitting your "Add Calculation" and just changing the output within the grid by using this renderer.

[Zach]

No problem, where do I access the renderer to input the above code for the column?

[Johnny Major]

It would be on the grid widget columns section. Docs

[Zach]

Thanks guys!