Elena

Hello all, First off, thank you for all the assistance given to me by the folks here at the community. I am hoping to pick your brains yet again: Is there an easier way to calculate the Median with SQL 2008 (which is the version that I am in, based on the plan that I purchased with Caspio)? Below is my code, which is not working: WITH Cnt AS (SELECT COUNT(*) AS cnt) ,RN AS (SELECT AutoID, ROW_NUMBER() OVER (ORDER BY Annual_Salaries) AS myNum FROM market_data ) SELECT AVG(1.*Annual_Salaries) AS median FROM RN JOIN Cnt on myNum IN ((cnt+1)/2,(cnt+2)/2) Any help will be put in the bank of 'never to be forgotten'. Elena

Good to know. I've received a warning a couple of weeks ago that my data usage is nearing my max, and that I will be charged accordingly. I know I have to edit some of the queries in some of my tabular pages. A few of my tables take a bit to load because of the queries. But does the size of the view table or source table have a causal link to this too?

At one point, I remember seeing a question about the label position issue when it is set to left. I searched this forum high and low but couldn't find it again so I don't know if it was answered. So my issue is, when I set my label position to left, a huge space between my label and my text field pops up. So I am limited to using top position for labels. Is there a way to minimize that space generated when label position is set to left - without going through workarounds of adding extra virtual fields, deleting wordings on labels, or creating html blocks to add back the label wordings? Any advice is much appreciated. Elena

What Jan suggested worked for my purpose. I have a similar graphic requirements based on conditions. My field is not a calculated field, but it is taking in values from a calculated field. I created an html block, then I referred to the actual field to point to the variables to be counted, instead of a calculated field (See "var score ="). Also, instead of pointing to app parameters within Caspio, I went ahead and sourced the images directly from my site. (see "https" references in the code). This made stuff a whole lot easier for me. My code is below. Note, I changed my url address, so you have to enter your own - to point to your image files. <SCRIPT LANGUAGE="JavaScript"> var score = '[@field:Status]'; score = parseFloat(score); if ((score>0)&&(score<=1)) document.write ('<img width=“15px" src="https://your_own_url/images/a_yellow_flag.png" />'); if ((score>1)&&(score<=2)) document.write ('<img width=“15px" src="https://your_own_url/images /a_blue_flag.png" />'); if ((score>2)&&(score<=3)) document.write ('<img width=“15px" src="https://your_own_url/images/ a_green_flagsmiley.png" />'); if ((score>0)&&(score<=0))document.write ('<img width=“15px" src="https://your_own_url/images/ a_red_flag.png" />'); </SCRIPT>

Hello there aa82, I am happy to report back that your recommendation worked. My filter is now operating fully as intended with a simple addition or "target". Thank you so much. E

Hi there, I'll try that and post back.

Hello, I am going to try to make sense and comprehensible in explaining. Essentially, I am trying to pick the maximum value (per record ID) out of the entire data table with multiple columns, and put this value in each corresponding row for each ID. For some reason, "SELECT ID, and GROUP BY ID" doesn't seem to be working in Caspio. My information is below: The problem with this code is that, it is finding the max alright, but it is putting the same value into each record. How can I correct this so that each record ID will have the right maximum value? For example: ID 123 = $97,900, ID 456 = $83,820 ID 789 = $59,730 Table name: market_data ID 123 456 789 PayscaleAverage $89,000 $76,200 $54,300 ERI_Median $97,900 $83,820 $55,321 Miliman_Avg $91,670.00 $78,486.00 $59,730.0 Here is the SQL Query: I am using UNPIVOT function. SELECT MAX(highestdata) AS maxnumber FROM market_data UNPIVOT (highestdata FOR Val IN (PayscaleAverage, ERI_Median, Miliman_Avg )) AS Number WHERE ID = [@field:ID_Lookup] I appreciate any help. Thank you in advance. P.S. Please, if you could, please do not recommend conditionals, such as, "WHEN Col1 < Col2 then..." because this table has over 200 columns.

Hello Walter, Thank you for the advice. Your code works, but I must be doing something wrong because it is counting columns with zero values as 1 as well. Effectively ignoring the ELSE. Here is the code based on your recommendation (I customized for my purpose). SELECT (CASE WHEN SUM(PayscaleAverage) >0 THEN 1 ELSE 0 END)+ (CASE WHEN SUM (ERI_Median) >0 THEN 1 ELSE 0 END)+ (CASE WHEN SUM(Miliman_Avg) >0 THEN 1 ELSE 0 END)+ (CASE WHEN SUM(TheLeagueCounties_Avg) >0 THEN 1 ELSE 0 END)+ (CASE WHEN SUM(TheLeagueCities_Avg) >0 THEN 1 ELSE 0 END) From Market_Data The problem is, I am getting a total number of 5, when actually the column "ERI_Median" has a value of 0. I should be getting 4. But it's not the case. On a slightly different note... any idea why "GROUP BY" doesn't seem to be acceptable in Caspio? I can't get GROUP BY to work.

Hi Matilda, Thank you for the guidance. Is there a way to shorten this conditional query? I have 25 columns that I am trying to count. So with the code above, I would have to do 25 fields for 25 queries. As experience told me, with Caspio the greater the queries, the slower the page to load when codes are embedded with html page. Can you recommend a SELECT query? Thank you again. E

Hello there, I am trying to count the number of columns where values are more than zero. I tried both "SUM" and "COUNT", in a calculated field, but my query is not working. I also want to sum the total count. I get this error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Can you please take a look and please let me know my mistake? SELECT ID, SUM(CASE WHEN PayscaleAverage >0 then 1 else 0 end) as one, SUM(CASE WHEN ERI_Median >0 then 1 else 0 end) as two, SUM(CASE WHEN Miliman_Avg >0 then 1 else 0 end) as three FROM market_data GROUP by ID I also tried "COUNT" but it's not working either. SELECT ID, COUNT(CASE WHEN PayscaleAverage >0 then 1 end) as one, COUNT(CASE WHEN ERI_Median >0 then 1 end) as two, COUNT(CASE WHEN Miliman_Avg >0 then 1 end) as three FROM market_data GROUP by ID Thank you, Elena ====== Edit: I would like to clarify that I only want to count the number of columns that has values greater than zero. I do not need to count the values themselves. Thank you.

Hi MayMusic, Thank you so much for the guidance. It worked! Thanks. Elena

I would like to clarify, the field that was I putting on the column section of the Pivot table has 600 records. What's happening was, because I had this field in the column, the Pivot is essentially counting the records as columns. So, naturally it would show errors.

Hello there, Is there a way to edit the label of the "value" side of the Pivot Table? Right now, whatever I choose comes out in the table result. For example: if I chose "Sum" from the drop-down of "Summarize value by:", that word "Sum of" automatically show in my table results, along with the actual label that I type in. If I chose "StdDev" the table results will show "StdDev of". I don't like showing them automatically. Is there a way to get rid of this? I attached a picture of what I meant, and a picture of my table, for reference. Thank you.

I figured out what's wrong. Pivot table reporting does not like view or table sources with too many fields in them. The fields are the columns (duh!). I recreated a table source with only 4 fields, but the same 600 records and Pivot Table reporting works perfectly now.