71305 - no subject - #Records Hi, we are trying to create a b

kdimitrov over 10 years ago

#Records Hi, we are trying to create a bar graph in the new SAIL interface, which shows new records by month (i.e. {January, 56}, {February, 25}, etc..... We are storing the date of the record's insertion (i.e. 18/06/2013 for example). Is it possible to convert these dates to a month value and aggregate the data over it? We are currently using the queryrecord() and type!Query as the tutorial has recommended, but it does not seem to allow that use case....

Discussion posts and replies are publicly visible

0 Sathya Srinivasan
Appian Employee
over 10 years ago

you can use the load() or with() method to query the data into a variable and apply TEXT(rf!yoruDateField, "MMMM") which will get you the format you need it in and once you have this data, you can reference the local variable into your SAIL expression
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 kdimitrov over 10 years ago

Thanks Sathya. I suppose that won't work for large volumes of data if I am going to load them locally and run apply() on all of these. Would it be better to create a view in MySQL that does MONTH() on the SQL column, creating a CDT for the view and querying that?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sathya Srinivasan
Appian Employee
over 10 years ago

correct. For large volume of data and if the data is coming from a DB, then creating a view where one of the column is a computed column (using to_date function of MySQL()) to get the month and using it will be ideal. If you are running aggregation, I would go one step further to create the aggregated view in the MySQL directly and map it to a Record type and use it. This will give you some performance benefit. Hope this helps.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 kdimitrov over 10 years ago

Hi Sathya, thanks for that info. We have created a view with 2 fields (label and data) that does the MONTH() and GROUP BY calculation, but we are unable to register a data type against it. It seems that Appian is demanding a primary key (the DDL script shows ALTER TABLE .... ADD PRIMARY KEY a_id ....). Is there any way go around that issue? We can't put a primary key on a view obviously.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sathya Srinivasan
Appian Employee
over 10 years ago

Can you not create another column called ID and in the view, configure it to show 'some' unique number? like rownum in Oracle sequence.nextval or something similar?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 kdimitrov over 10 years ago

Hi Sathya, yes, we added the original table's primary key and changed the "data" column to "xsd:long" since aggregating on COUNT() in MySQL produces bigint(21) result and it worked like a charm. Thanks for your help!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel