Character limit for expression?

[yuiweixi]

There was a previous question on this but the question wasn't answered since the person realized the problem was something else. 

I'm using script to generate a expression that is over 1000 line long, and it doesn't seem to be going through. This is the error I'm getting. I reduced the line counts of the expression and the error seems to disappear at around 200 lines. So, is there a character limit to how long expression can be?

Alternatively, is there a better way to implement 2D arrays with AppSheet that isn't 1000 line of nested IFS()? Specifically what I'm trying to do is implement this weight by age chart (attached) for children growth to get the percentile a child is in. 

[WillowMobileSys]

There most assuredly is a better way just based on the shear size of the expression.  I can't even imagine a need for a 200 line expression, let alone 1000 lines long.   Note that an expression of that size will likely be SUPER slow if you have any size the table at all.

I am not clear on exactly what it is you are trying to do.  Are you trying to create the data points that produced the chart?

[yuiweixi]

Hi there! Thank you for answering. Would be so amazing if you could help point me to the right direction. What I'm trying to is to do is take a child's weight and their age and compute the percentile of children growth they lie in.

Say a child is 2 month old and weighs 4 kilograms, then I would want to be able to output 3rd percentile according to the pink chart attached above. The data ranges from 1-60 months of age and each month has 11 percentile categroies.

This is a snippet of how the expression is structured. This is all contained in an IFS statement and repeats 60 times for each month (while swapping out the weight categorization for each month):

[Patient Linked].[Months Old] =0,
IFS (
[Weight kg] <=2.5,
"0th-3st percentile",
[Weight kg] <=2.6,
"3rd-5th percentile",
[Weight kg] <=2.9,
"5th-15th percentile",
[Weight kg] <=3,
"15th-25th percentile",
[Weight kg] <=3.3,
"25th-50th percentile",
[Weight kg] <=3.7,
"50th-75th percentile",
[Weight kg] <=3.9,
"75th-85th percentile",
[Weight kg] <=4.2,
"85th-95th percentile",
[Weight kg] <=4.3,
"95th-97th percentile",
[Weight kg] <=4.6,
"97th-99th percentile",
[Weight kg] <=4.6,
"+99th percentile"
),
[Patient Linked].[Months Old] =1,
IFS (
[Weight kg] <=3.4,
"0th-3st percentile",
[Weight kg] <=3.6,
"3rd-5th percentile",
[Weight kg] <=3.9,
"5th-15th percentile",
[Weight kg] <=4.1,
"15th-25th percentile",
[Weight kg] <=4.5,
"25th-50th percentile",
[Weight kg] <=4.9,
"50th-75th percentile",
[Weight kg] <=5.1,
"75th-85th percentile",
[Weight kg] <=5.5,
"85th-95th percentile",
[Weight kg] <=5.7,
"95th-97th percentile",
[Weight kg] <=6,
"97th-99th percentile",
[Weight kg] <=6,
"+99th percentile"
),
[Patient Linked].[Months Old] =2,
IFS (
[Weight kg] <=4.4,
"0th-3st percentile",
[Weight kg] <=4.5,
"3rd-5th percentile",
[Weight kg] <=4.9,
"5th-15th percentile",
[Weight kg] <=5.1,
"15th-25th percentile",
[Weight kg] <=5.6,
"25th-50th percentile",
[Weight kg] <=6,
"50th-75th percentile",
[Weight kg] <=6.3,
"75th-85th percentile",
[Weight kg] <=6.8,
"85th-95th percentile",
[Weight kg] <=7,
"95th-97th percentile",
[Weight kg] <=7.4,
"97th-99th percentile",
[Weight kg] <=7.4,
"+99th percentile"
)

[yuiweixi]

The other solution that I see but would be tedious is to create 60 different columns and write an Expression for each one of them. Seems a lot of repetitive manual work and there must be a better way that I'm failing to see.

[Suvrutt_Gurjar]

I think as @WillowMobileSys  righly guided, writing such a large expression is impractical and in general cannot be expected to be supported by any system. Debugging such a huge expression or making any changes later  will also be a challenge.

You may want to evaluate if you can come up with a range matrix as mentioned in the posts below. So for each age and weight range,  if there are certain percentile values, you may not have so many different individual percentile values for each combination of age and weight.  You could then have a smaller lookup table of weight vs age for percentile values.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Risk-Matrix-Calculation/m-p/354227

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Best-Way-to-Select-From-A-Matrix/m-p/308168

[WillowMobileSys · Accepted solution]

Yes, as @Suvrutt_Gurjar suggests you want to use a lookup table.  You would have it setup something like this:

ID	Age(Months)	Min Weight	Max Weight	Percentile
71352216	2	0	4.4	0th-3st
32836258	2	4.4	4.5	3rd-5th
68707655	2	4.5	4.9	5th-15th
44294852	2	4.9	5.1	15th-25th
97700600	2	5.1	5.6	25th-50th
31918377	2	5.6	6	50th-75th
29888201	2	6	6.3	75th-85th
...	...	...	...	...

To use this table, you would ask in a Form for the Age and Weight of the child.  Then you would use those in an expression like below to lookup the Percentile:

ANY(SELECT(Percentiles[Percentile], AND([Age] = [_THISROW].[Age],
                                        [Min Weight] > [_THISROW].[Weight],
                                        [_THISROW].[Weight] <= [Max Weight])))

There is work in creating the table but its a one time effort, unless the Percentiles shift but then it's just simple adjustments in the table.

[yuiweixi]

This solution works exactly how I needed it!! Still have so much to learn about  AppSheet... Thank you so much for this desperately needed pointer:)