OUTPUT ERROR: ReferenceError: "myinfotable" is not defined.

[xiaoqw]

Hi, Can someone help me to fix this problem ASAP? thank you.

[Radu]

Hi,

Where is this infotable declared? Should it be a service parameter, or is it a thing property? In case it's a property from the same thing as the service, it should be referenced as me.myinfotable

[xiaoqw]

I put infotable under datashape and after I change from myinfotable to me.myinfotable, it pop this new error: 

[Constantine]

Hello,

When you type "me.myinfotable", it means that there's a property called "myinfotable" on the thing where you define this service, in your case on "GF Datatable PV UHT 1000 ml". Do you have this property defined there? If yes, then where does the data come from, how do you initialize it?

If no and you want to aggregate some data from this data table, then you should probably do something along those lines (check Help for details about those services):

var myinfotable = me.QueryDataTableEntries(...);
var result = Resources["InfoTableFunctions"].Aggregate({
  t: myinfotable,
  ...
});

Regards,
Constantine

[xiaoqw]

ok. thank you. if I say I want to use datatable's data to do an aggregation how to do that way?

[Constantine]

Like that:

var values = me.CreateValues();

values.property1 = value1;
values.property2 = value2;
values.property3 = value3;

var entries = me.QueryDataTableEntries({
	maxItems: ...,
	values: values,
	query: ...
});

var result = Resources["InfoTableFunctions"].Aggregate({
	t: entries,
	columns: ...,
	aggregates: ...,
	groupByColumns: ...
});

You will find more about service parameters that I replaced with ... in ThingWorx Help.

/ Constantine

[Constantine · Accepted solution]

In your case the simplest way to go is this (don't do it on the large data tables):

var result = Resources["InfoTableFunctions"].Aggregate({
	t: me.QueryDataTableEntries({ maxItems: 1000000 }),
	columns: 'KPI_Value',
	aggregates: 'SUM',
	groupByColumns: 'No'
});

/ Constantine

[xiaoqw]

And then if I want to sum data monthly, how to do that? I try getMonth() function alr, but it seems like summarize all data so far I key in. 

var result = Resources["InfoTableFunctions"].Aggregate({
	t: me.QueryDataTableEntries({ maxItems: 1000000 }),
	columns: 'KPI_Value',
	aggregates: 'SUM',
	groupByColumns: 'Date.getDate()'
});

 

[Constantine]

I would do it like this:

1. Add a field "month" to your DataShape for the data table;
2. Fill this field with the correct month when you insert data into the data table;
3. Use this field in groupByColumns, like that:

var result = Resources["InfoTableFunctions"].Aggregate({
	t: me.QueryDataTableEntries({ maxItems: 1000000 }),
	columns: 'KPI_Value',
	aggregates: 'SUM',
	groupByColumns: 'month'
});

/ Constantine

[xiaoqw]

thank you for your reply. but this method I try before, i prefer automatically sum weekly value and then get the monthly value as customer requirement. 

[Constantine]

Why don't you use two fields "month" and "week" then? Like this you'll be able to sum by both of them separately. Also, if you have a list of weekly sums, then getting a monthly sum is a rather straightforward service -- just iterate through them in a loop and calculate the total...

/ Constantine

[xiaoqw]

U mean I just create datashape for Month and don't show in datatable. Yesterday I try to sum weekly data per month but it will sum all the data I have. May I know How to do the code for each month? Sorry I Really need points for it.

[xiaoqw]

And After that, How to put the sum value into Mashup? I also confusing about that.

[Constantine]

Here, this example is the best I can do to explain my approach.

/ Constantine

[xiaoqw]

thank you so much. I will go through it. 

[xiaoqw]

Hi Mr Constantine, also your method is correct. but in my previous code also include data filter in the service. but now I have no idea how to combine data filter and your code in one service. Where should I put data filter based on your code?

[Constantine]

Hello,

For the data filter, add "query" parameter to QueryDataTableEntries service in QueryByMonth and QueryByWeek. Bind this parameter to the output of the Data Filter widget.

/ Constantine

[xiaoqw]

Thank you. it worked

[xiaoqw]

also, how to do yearly, is it the same method as week and month? just now I try but it shows 119 rather than 2019. what is the problem?

[Constantine]

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/getYear

119 is correct.

[xiaoqw]

Thank you so much. I will appreciate that you help me a lot. 

[Constantine]

No problem, that's what the Community is for.