Jump to content


Caspio Guru
  • Posts

  • Joined

  • Last visited

  • Days Won


Everything posted by Elena

  1. 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
  2. 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?
  3. 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
  4. 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>
  5. 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
  6. 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.
  7. 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.
  8. 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
  9. 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.
  10. Hi MayMusic, Thank you so much for the guidance. It worked! Thanks. Elena
  11. 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.
  12. 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.
  13. 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.
  14. Hi Matilda, Sorry for the lateness of this response. I will try your edited code today, and post a progress report accordingly. Elena
  15. Hello all, I tried to create a report with Pivot Table, I used a view table as my source of data. All were great, columns, rows, summations, formatting, and formulas. Then when I go preview the report, it tells me this "Pivot table cannot have more than 200 columns". I don't have 200 columns, actually I only have 4 columns. Can anyone please help me figure out what I was doing wrong? Any help is greatly appreciated. Elena
  16. Hello all, Please help. I will appreciate any advice. I've been trying to pass the values of a calculated field to an actual field in my table. I did scour the entire forum for knowledge and tried everything I could find as methods. So far, none worked for me. I tried these: 1) Passing values by hiding the actual field and choosing "Data Source Fields" to receive values. This doesn't work. 2) I tried putting a virtual field, hiding it and choosing "on load receive value parameter", choosing the calculated field, then "on exit pass field value parameter". Then I used the virtual parameter to load my actual field. This not only did not work, it caused my details to load so very slowly. 3) Then I also tried the javascript below. 4) I also tried a combination of all of these, with the javascript in the footer. So far, none has worked. <SCRIPT LANGUAGE="JavaScript"> function concatenate() { var x = document.getElementsByName("[@calcfield:44#]")[0].value; document.getElementById("InsertRecordNew_Job_Grade").value = x; } document.getElementById("caspioform").onload=concatenate; </SCRIPT>
  17. I solved this issue. My problem was that the precision on my calculated field 3, was not set to the same decimal and rounding setup as my look up table. I fixed this accordingly by proper formatting. Thank you.
  18. Hello Maymusic, My apologies for the lateness of this acknowledgment. I did not see your response to me until tonight. Thank you so much for helping me. The code worked. Elena
  19. I must add that the lookup table (MatrixPayOut) with columns, "YOS" and "RP" have the same exact matching values with the main table that I am using for my tabular report. As you can surmise the values from my main table (Census) are generated using calculations. I checked the rounding off format to see if that is causing the problem. But as far as I can see, the values generated out of the calculated fields from the table "Census", generate the same exact values and rounding off as the lookup table,"MatrixPayOut". I hope I am making sense in this explanation. Thank you, Elena
  20. Hi Iren, Thank you for responding. The formula for my calculated fields: Calculated Field 3: Datediff(year, [@field:Date_Hire],GetUTCDate()) Calculated Field 6: [@field:Current_Salary] - ([@calcfield:8])) / (([@calcfield:4]) - ([@calcfield:8])) The calculated fields 8 and 4, are both SQL SELECT syntax from a separate lookup table.
  21. Hello, I am trying to select a value out of a look up table separate from my main table. I have a tabular report. The SQL syntax that I have (below) works, but it selects only those with zero (0) value on the "RP" column. Anything more than zero, I get blanks. I actually thought this could help in showing specific values conditionally from a lookup table. SELECT Percent_Matrix FROM MatrixPayOut WHERE YOS = [@calcfield:3] AND RP = [@calcfield:6] Can you please help me rewrite the syntax? I would like to know if an inner join method is a better approach? Thank you and Happy Holidays. Elena
  22. Hi Jan, Thank you again for helping. The closing </div> does not like to stay in the footer for some reason. It keeps on going to the header to close the open tag there. It isn't working for me. Thank you anyway.
  23. Hello again, Does anyone know how to format a column or columns in tabular report page, without modifying the "Style"? I only wish to format a few columns to align in the middle. The rest of the columns in my tabular report must remain aligned left. Any workaround by way of Javascript? Anyone? Please help. I attached a snapshot of my issue (for drama) :-) The "Years of Service (YOS)" column is what I wanted to align in the middle. Thank you in advance, I appreciate all the valuable knowledge in here. Elena
  • Create New...