469,326 Members | 1,320 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

How to output with a control variable inside cfquery name?

769 512MB
Hey Everyone,

Well i am having a problem outputting for my report and hoping someone can explain what i am doing wrong.

What the report is about is comparing my company's part price's to d and h, synnex and techdata. What i was told to do was take are part numbers and combine them with d and h, synnex and techdata to display only one column of parts (did this). Then i was told to create a cfloop using the query i created to combine the parts (did this). then inside the cfloop i created a cfif for each company, doing the cfif is suppose to get the price for each company(did this).Then i was told to add a loop control variable so that if one price is empty it will skip it instead of add the next price.

The problem is that i was able to get one price to output, but now i can not get any of the prices to output at all an not sure what i am doing wrong. Before i added a loop control variable i was able to output techdata's prices by doing #techdata.cost# but now with the loop control variable i am not able to. I was also not able to output synnex and techdata before adding the loop control variable, but i was told that how i was outputting it could be the problem. if anyone could tell me what i am doing wrong, atleast to how i am displaying techdata i would really appreciate it. Here is what i currently have.

Expand|Select|Wrap|Line Numbers
  1. This combines all the parts
  2. <cfquery name="matchmanu" datasource="Configurator">
  3. select a.[hc part number]'hcpartnumber',a.mpn,a.Mfg,a.lastbuyquote,a.[description],b.prodid,c.distisku,c.distiid
  4. from [svr-htssqldb].configurator.dbo.[Master Parts List] a
  5. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_prod b
  6. on a.mpn=b.mfpn
  7. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_metamap c
  8. on b.prodid=c.prodid
  9. where a.lastbuyquote is not null and (c.distiid='c00030' or c.distiid='c00055' or c.distiid='c00791')
  10. </cfquery>
  11.  
  12.  
  13. <table width="95%" align="center">
  14. <thead>
  15. <tr>
  16. <th>Manu Part number </th>
  17. <th>Techdata_Price</th>
  18. <th>Synnex Price</th>
  19. <th>D&H Price</th>
  20. </tr>
  21. </thead>
  22. <cfset alt=0>
  23. <cfset classStr="">
  24.  
  25. <cfoutput>
  26. <cfloop query="matchmanu">
  27. <cfset t = 1>
  28. this gets the prices for techdata
  29. <cfif distiid EQ 'C00030'>
  30. <cfquery name="techdata#t#" datasource="Distributor">
  31. Select  COST
  32. From dbo.PRODUCTS
  33. where PART_NUM = '#matchmanu.distisku#'
  34. </cfquery>                          
  35. <cfelse>
  36.  
  37. </cfif>
  38. <cfset t = t + 1>
  39.  
  40. <cfset s = 1>
  41. this gets the prices fo synnex
  42. <cfif distiid EQ 'C00055'>
  43. <cfquery name="synnex#s#" datasource="Distributor">
  44. Select Unit_Cost__wo_PromoRebate
  45. From dbo.tbl_synnex_price
  46. where SYNNEX_SKU = '#matchmanu.distisku#'
  47. </cfquery>
  48. <cfelse>
  49. </cfif>
  50. <cfset s = s + 1>
  51.  
  52. <cfset d = 1>
  53. this gets the prices for dandh
  54. <cfif distiid EQ 'C00791'>
  55. <cfquery name="dandh#d#" datasource="Distributor">
  56. Select unit_cost
  57. From dbo.tbl_dandh_price
  58. where dandh_item_num = '#matchmanu.distisku#'
  59. </cfquery>
  60. <cfelse>
  61. </cfif>
  62. <cfset d = d + 1>
  63.  
  64.  
  65.  
  66. <cfif alt eq 0>
  67.     <cfset classStr="">
  68.     <cfset alt=1>
  69. <cfelse>
  70. <cfset alt=0>
  71. </cfif>
  72. <tr>
  73. <td class="#classStr#">
  74. #matchmanu.distisku#
  75. </td>
  76. this outputs techdata
  77. <td class="#classStr#">
  78. #techdata#t#.COST#
  79. </td>
  80. thisoutputs synnex
  81. <td class="#classStr#">
  82. #synnex.Unit_Cost__wo_PromoRebate#
  83. </td>
  84. this outputs dandh
  85. <td class="#classStr#">
  86. #dandh.unit_cost#
  87. </td>
  88. </tr>
  89. </cfloop>
  90. </cfoutput>
  91. </table>
Thank you,
Rachel
Feb 3 '09
109 11559
bonneylake
769 512MB
Hey Acoder,

Well i tried your example code but the lowest value is not outputting, but i think its because synnex is not right or else i shouldn't be outputting #lowest# an should be using something else. Here is what i currently have.

Expand|Select|Wrap|Line Numbers
  1. <cfif techdata.COST neq 0.00> 
  2.   <cfset lowest = techdata.COST> 
  3. <cfelseif synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  4.   <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  5. <cfelse> 
  6.    <cfset lowest = dh.unit_cost> 
  7. </cfif> 
  8. <cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0 and lowest gt synnx.Unit_Cost__wo_PromoRebate> 
  9.   <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  10. </cfif> 
  11. <cfif dh.unit_cost neq 0.0 and lowest gt dh.unit_cost> 
  12.    <cfset lowest = dh.unit_cost> 
  13. </cfif> 
  14.  
  15. <td class="#classStr#">
  16. #lowest#
  17. </td>
an well unit_cost__wo_promorebate is a (float,null) ,synnex_sku is a numeric(18,0),null and the #synnxsku# is a varchar.

i did the following an this seems to get me a bit closer to my goal

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="synnx" datasource="Distributor">
  2. Select Unit_Cost__wo_PromoRebate
  3. From dbo.tbl_synnex_price
  4. where SYNNEX_SKU = '#val(synnxsku)#'
  5. </cfquery>
the thing is though doing it that way a lot of my values i should have is missing.
An example i am suppose to have

price
price
no price
no price
price
price

and its doing it like this

price
no price
no price
no price
price
price

Any suggestions on how to make it put the value that is not numeric?

Thank you,
Rach
Feb 11 '09 #51
acoder
16,027 Expert Mod 8TB
When a company doesn't have a price, is the price field NULL or does the table not have a record? Perhaps you could leave out records with value null by adding IS NOT NULL checks in the query. Then to check if it's not got a price, you could just check the recordcount of the query.
Feb 11 '09 #52
bonneylake
769 512MB
Hey Acoder,

The way i am checking it is by running the following in sql server

exec usp_matching_vendor_distisku_from_MPL

an according to it, it says the value is NULL. But its really hard to go through each table an check if there is an actual value or not since its millions of records.But i know that it is possible that it does not have a record for it because some companys wont have a price for a part or even the part.

I did the following for the where an now for all the values with nothing i get 0.00. Do i need to do the record count, if so how would i do the record count?

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="synnx" datasource="Distributor">
  2. Select Unit_Cost__wo_PromoRebate
  3. From dbo.tbl_synnex_price
  4. where SYNNEX_SKU is not null and (SYNNEX_SKU = '#val(synnxsku)#' )
  5. </cfquery>

But i realize i got another problem. Some of the prices are not matching up right. Some match up right, some are not. Is there anyway to make sure all the prices match up correctly? Because right now some spots are having a value when its suppose to be null or some do not have a value where a value is suppose to be.An i am having this trouble with all prices not just synnex.

Besides that the lowest value is almost working correctly. If i have 3 values all on one row it is getting the lowest value, but if i have 2 values an a blank it doesn't get the lowest value.


Thank you,
Rach
Feb 11 '09 #53
acoder
16,027 Expert Mod 8TB
The recordcount is a property of the query result, e.g. synnex.recordcount.

The "is not null" should be for the cost price, so you only get valid prices, though if it's 0.0, you could add a check for that too.
Feb 11 '09 #54
bonneylake
769 512MB
Hey Acoder,

Are you saying something like this for the where

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="synnx" datasource="Distributor">
  2. Select Unit_Cost__wo_PromoRebate
  3. From dbo.tbl_synnex_price
  4. where Unit_Cost__wo_PromoRebate is not null and (SYNNEX_SKU = '#val(synnxsku)#') 
  5. </cfquery>
an then for the record count i did something like this

Expand|Select|Wrap|Line Numbers
  1. #dollarformat(synnx.recordcount.Unit_Cost__wo_PromoRebate)#
but got the error Error resolving parameter

I also have another question. If in my stored procedure i get the values for the following prices

Expand|Select|Wrap|Line Numbers
  1. techdata    synnex       d and h
  2. price         price            no
  3. price         price             no
  4. price          no               no
  5. price          no               no
  6. price          price           no
if i do in my report a where for part num = techdata (techdata coming from stored procedure) will i get a different output in my report then in my stored procedure? reason i ask is because a good amount of the values i got in my stored procedure are not matching up to the values in my report. Some match some don't so just want to know if my report is wrong or if that is how it should be coming out based on i am comparing it to the part num instead of just outputting techdata. because right now when i output to report i get the following instead of the above.

Expand|Select|Wrap|Line Numbers
  1. techdata      synnex     d and h
  2.  $34.47       $33.82      $0.00  
  3. $34.47        $0.00        $0.00  
  4. $0.00         $0.00         $0.00  
  5.  $0.00        $0.00           $0.00  
  6. $0.00        $82.38         $82.53  
Thank you,
Rach
Feb 11 '09 #55
acoder
16,027 Expert Mod 8TB
Recordcount gives you the number of records returned by the query, so you'd use it like this:
Expand|Select|Wrap|Line Numbers
  1. <cfif synnex.recordcount neq 0>
  2. <!--- now compare --->
Let's deal with one problem at a time otherwise it can get more difficult to follow what's going on.
Feb 11 '09 #56
bonneylake
769 512MB
Hey Acoder,

I think i got this right. Its putting the lowest value out of all 3 an not using 0.00, unless all 3 have 0.00 then its putting 0.00. An that sounds like how it should be working. This is what i currently have, i bolded where i added to it. If this is right let me know whats next.

Expand|Select|Wrap|Line Numbers
  1.  
  2. <cfif techdata.recordcount neq 0>
  3. <cfif techdata.COST neq 0.00> 
  4. </cfif>
  5.   <cfset lowest = techdata.COST> 
  6. <cfelseif synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  7.   <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  8. <cfelse> 
  9.    <cfset lowest = dh.unit_cost> 
  10. </cfif> 
  11. <cfif synnx.recordcount neq 0>
  12. <cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0 and lowest gt synnx.Unit_Cost__wo_PromoRebate> 
  13.   <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  14. </cfif> 
  15. </cfif>
  16. <cfif dh.recordcount neq 0>
  17. <cfif dh.unit_cost neq 0.0 and lowest gt dh.unit_cost> 
  18.    <cfset lowest = dh.unit_cost> 
  19. </cfif>
  20. </cfif>
  21.  
  22.  

Thank you,
Rach
Feb 11 '09 #57
acoder
16,027 Expert Mod 8TB
Almost. You could combine everything together - something like this:
Expand|Select|Wrap|Line Numbers
  1. <cfset lowest = -1>
  2. <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
  3.   <cfset lowest = techdata.COST> 
  4. </cfif>
  5. <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  6.   <cfif lowest eq -1>
  7.     <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  8.   <cfelseif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  9.     <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  10.   </cfif>
  11. </cfif>
and similar code for dandh. That would avoid repetition.

If lowest is still -1 then you know that no companies have prices for that part.
Feb 12 '09 #58
bonneylake
769 512MB
Hey Acoder,

Would it be alright instead of doing -1 to just do 0 instead? just afraid that doing -1 would mess up the > -+ 3% when i get the lowest and compare it to my company cost.But also afraid if anyone saw it as -1 they might get confused. But would this be correct way to do the lowest cost? Trying it this way looks correct though.

Expand|Select|Wrap|Line Numbers
  1.  <cfset lowest = 0>
  2.  <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
  3.      <cfset lowest = techdata.COST>
  4.       </cfif>
  5.     <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  6.       <cfif lowest eq 0>
  7.         <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  8.       <cfelseif lowest gt synnx.Unit_Cost__wo_PromoRebate>
  9.         <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  10.      </cfif>
  11.    </cfif>
  12.  <cfif dh.recordcount neq 0 and dh.unit_cost neq 0.00> 
  13.       <cfif lowest eq 0>
  14.         <cfset lowest = dh.unit_cost> 
  15.       <cfelseif lowest gt dh.unit_cost>
  16.         <cfset lowest = dh.unit_cost> 
  17.      </cfif>
  18.    </cfif>
Thank you,
Rach
Feb 12 '09 #59
acoder
16,027 Expert Mod 8TB
It wouldn't matter because you can check for -1, so you'd know there were no prices, so when it comes to displaying, you could output "N/A" for the price difference. If it's not -1 (or 0 if you choose), you have a lowest price which you can compare with the company price.
Feb 12 '09 #60
bonneylake
769 512MB
Hey Acoder,

Alrighty just wanted to make sure it be ok to do it that way. But i do have another problem which is going to affect doing the < - + 3% part.

Well i am trying to just output the cost for my company. The problem i am having is the way its outputting. If i do it this way it matching up the cost with the lastbuyquote (or where the cost should be) but instead of displaying the cost its displaying the lasbuyquote.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="acompare" datasource="Configurator">
  2. select Cost
  3. from dbo.[Master Parts List]
  4. where Cost = #matchmanu.lastbuyquote#
  5. </cfquery>
an then if i do it this way, it outputs the cost but it displays the same cost down the whole column an doesn't match up property so all i see is 45 all the way down.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="acompare" datasource="Configurator">
  2. select Cost
  3. from dbo.[Master Parts List]
  4. where lastbuyquote = Cost
  5. </cfquery>-
Any suggestions on what i am doing wrong?

Thank you,
Rach
Feb 12 '09 #61
acoder
16,027 Expert Mod 8TB
What's the last buy quote? What you need to use in the where clause is a foreign key that matches with the part number. Perhaps a look at the fields in that table may help.
Feb 12 '09 #62
bonneylake
769 512MB
Hey Acoder,

Lastbuyquote is one of the fields that appear in the master parts list table,but it is used in the stored procedure.So i figured if i used the lastbuyquote from the stored procedure and matched it up to the cost that it would match up cost to the correct lastbuyquotes. but there are a lot of fields in the table an the only field in there that holds the part number (which i am outputting using the stored procedure) is mpn. An i don't think i can just use mpn without doing matchmanu.mpn because it will output all the mpn instead of just the ones used in the report. so is the problem the fact that i am using a column from a different cfquery or stored procedure that is causing the trouble?

Thank you,
Rach
Feb 12 '09 #63
acoder
16,027 Expert Mod 8TB
If you can get the price from the main stored procedure, then there's no need for a separate query to get the cost/price. If you can't, then look at the fields in the Master Parts Table and see which one is for parts number. Then, in the query, match that field against whichever field is the parts number in the stored procedure.
Feb 12 '09 #64
bonneylake
769 512MB
Hey Acoder,

Your second idea worked perfectly! I feel blond for not seeing that because it makes perfect sense! So how would i begin doing the > - + 3% part?

Thank you :),
Rach
Feb 12 '09 #65
acoder
16,027 Expert Mod 8TB
If you want to work out the price difference as a percentage:
Expand|Select|Wrap|Line Numbers
  1. (lowest - company price)/company price
I've not tested, but that would be the idea.
Feb 12 '09 #66
bonneylake
769 512MB
Hey Acoder,

would something like this work for it?

Expand|Select|Wrap|Line Numbers
  1. (#acompare.Cost# - (#lowest# * 3 / 100)) and (#acompare.Cost# + (#lowest# * 3 / 100))
an how would i output just the results (final number). When i do it above i get the whole line appearing with the values.i tried <cfset test = the above but i ended up getting errors.

Thank you,
Rach
Feb 12 '09 #67
acoder
16,027 Expert Mod 8TB
What exactly do you want to display? The price difference, the % price difference, 3% of the lowest price?
Feb 12 '09 #68
bonneylake
769 512MB
Hey Acoder,

Basically i am trying to see my company's price difference of > + - 3% from the lowest cost distributor (company with the lowest price)

Thank you,
Rach
Feb 12 '09 #69
acoder
16,027 Expert Mod 8TB
So, if it's less than +- 3%, you don't want to display it?
Feb 12 '09 #70
bonneylake
769 512MB
Hey Acoder,

Yes basically they don't want to see it unless its 3% and up.

Thank you,
Rach
Feb 12 '09 #71
acoder
16,027 Expert Mod 8TB
So you could try
Expand|Select|Wrap|Line Numbers
  1. <cfset cost = aCompare.cost>
  2. <cfset pricediff = (lowest - cost)/cost)>
Then check that it's greater than 3 or less than -3. If it is, display, otherwise just display nothing or a space.
Feb 12 '09 #72
bonneylake
769 512MB
Hey Acoder,

Don't think i got this right. Am i suppose to be using lowest for the gt or lt or am i suppose to be using pricediff?An i am getting and error with the ) it says Invalid parser construct

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfset pricediff = (#lowest# - cost)/cost)>
  3. <cfif lowest gt 0.03 or lowest lt -0.03>
  4. #cost#
  5. <cfelse>
  6.  
  7. </cfif>
Thank you,
Rach
Feb 12 '09 #73
acoder
16,027 Expert Mod 8TB
pricediff, not lowest.
Feb 12 '09 #74
bonneylake
769 512MB
Hey Acoder,

So would something like this be correct? or does the pricediff gt and lt need to be on the same line?

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfset pricediff = ((#lowest# - cost)/cost)>
  3. <cfif pricediff gt 0.03>
  4. #cost#
  5. <cfelseif pricediff lt -0.03>
  6.  
  7. </cfif>
also i am getting an error saying

An error occurred while evaluating the expression:

pricediff = ((#lowest# - cost)/cost)

Cannot convert to number.

An i think i got one part confused. They want to see > + - 3%. I been thinking the whole time its <.



Thank you,
Rach
Feb 12 '09 #75
acoder
16,027 Expert Mod 8TB
It should be on one line. Check the values of cost and lowest to see what they are (display them for testing purposes).
Feb 12 '09 #76
bonneylake
769 512MB
Hey Acoder,

Confused on what you mean Check the values of cost and lowest? just output them like #acompare.cost# output it an then change it from that to #lowest# and output it? bit confused on how to check.

so everyone on one line should be something like this?

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfset pricediff = ((#lowest# - cost)/cost)>
  3. <cfif pricediff gt 0.03 and pricediff lt -0.03>
  4. #cost#
  5. <cfelse>
  6.  
  7. </cfif>
Thank you,
Rach
Feb 12 '09 #77
acoder
16,027 Expert Mod 8TB
Yes, just output them to test what their values are, so you can see what values you're working with and if they're correct or not.
Feb 13 '09 #78
bonneylake
769 512MB
Hey Acoder,

According to what i am looking at, there both outputting numbers. Its getting the correct value for the lowest company by doing #lowest# and when i do the cost, the cost is matching up correctly and its a number as well by doing #acompare.Cost#. But i know cost is a float and the company price is numeric, i don't know if that makes a difference or not as to why the pricediff wont work.

i know the problem has to be with cost because when i did the following, i got the error Division by zero is not allowed. I know that cost is a float value and lowest is numeric. but not sure how i would convert float to numeric


Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfset pricediff = ((#lowest# - '#val(cost)#')/'#val(cost)#')>
  3. <cfif pricediff gt 0.03>
  4. #cost#
  5. <cfelseif pricediff lt -0.03>
  6.  
  7. </cfif>
Thank you,
Rach
Feb 13 '09 #79
acoder
16,027 Expert Mod 8TB
Don't put the val() in quotes. Is cost ever 0? If it is, it would cause zero division errors?

If it's 0, a comparison doesn't make sense, so a blank or "N/A" would have to be output.
Feb 13 '09 #80
bonneylake
769 512MB
Hey Acoder,


Actually doing this seemed to help. I am not getting errors anymore an now some of the values are 0 and some are not.But i am not sure if this is the correct solution or not.

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfif IsNumeric(cost)>
  3. <cfset pricediff = ((#lowest# - cost)/cost)>
  4. <cfif pricediff gt 0.03>
  5. #cost#
  6. <cfelseif pricediff lt -0.03>
  7.  
  8. </cfif>
  9. </cfif>
  10.  
i changed the 0 that appears underneath cfelseif statement to n/a an that didn't seem to make a difference at all to the results.

but here is what i had before when i was just outputting #acompare.cost#

for line 194 i had 16 49
for line 292 i had 432.8000

an then when i did it with the isnumeric (above) this is what i get

for line 194 i get 0
for line 292 i get 432.8000

i also did a test with the calculator to make sure its right i did for the first one i did
(($9.98 - $16.49)/$16.49) and got the result -0.394 (an the numbers continue)

an then for the second one i did

(($453.97 - $432.80)/$432.80) and i got the result 0.048 (an the numbers continue)

Thank you,
Rach
Feb 13 '09 #81
acoder
16,027 Expert Mod 8TB
You need to combine lines 4 and 6:
Expand|Select|Wrap|Line Numbers
  1. <cfif pricediff gt 0.03 and pricediff lt -0.03>
Do you want to output the cost or the price difference?

What are lines 194 and 292 that you mentioned in the previous post? Are these in the table? Also, does 16.49 have a space, i.e. is it "16 49"? If it is, the IsNumeric() will be false.
Feb 13 '09 #82
bonneylake
769 512MB
Hey Acoder,

Definately want to output the price different so instead of using #cost# would i use #pricediff#?. Line 194 and 292 appear in the report.used them because there the first 2 that have something in the price diff column an my bad its 16.49 (typed it wrong).

So would this be correct?

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfif IsNumeric(cost)>
  3. <cfset pricediff = ((#lowest# - cost)/cost)>
  4. <cfif pricediff gt 0.03 and pricediff lt -0.03>
  5. #pricediff#
  6. <cfelse>
  7. N/A
  8. </cfif>
  9. </cfif>
Thank you,
Rach
Feb 13 '09 #83
acoder
16,027 Expert Mod 8TB
Yes, but as well as a check to make sure cost is numeric, you may want to add a check that it's not 0.
Feb 13 '09 #84
bonneylake
769 512MB
Hey Acoder,

So would this be correct? not sure if i got the cost neq in the right spot or not. not sure if it should be before or after isnumeric

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfif IsNumeric(cost)>
  3. <cfif cost neq 0>
  4. <cfset pricediff = ((#lowest# - cost)/cost)>
  5. <cfif pricediff gt 0.03 and pricediff lt -0.03>
  6. #pricediff#
  7. <cfelse>
  8. N/A
  9. </cfif>
  10. </cfif>
  11. </cfif>
Thank you,
Rach
Feb 13 '09 #85
acoder
16,027 Expert Mod 8TB
Yes, or you could combine the two:
Expand|Select|Wrap|Line Numbers
  1. <cfif IsNumeric(cost) and cost neq 0.0>
Feb 13 '09 #86
bonneylake
769 512MB
Hey Acoder,

I tried it the way i had it and tried it your way an now all the values for the price diff say n/a. I think its cause when you do the price diff at the beginning of all of them they had 0.0 in front? Any suggestions?

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfif IsNumeric(cost) and cost neq 0>
  3. <cfset pricediff = ((#lowest# - cost)/cost)>
  4. <cfif pricediff gt 0.03 and pricediff lt -0.03>
  5. #pricediff#
  6. <cfelse>
  7. N/A
  8. </cfif>
  9. </cfif>
Thank you,
Rach
Feb 13 '09 #87
acoder
16,027 Expert Mod 8TB
That can only mean it's failing the IsNumeric or neq 0 test. Is cost a float or is it stored as a string?
Feb 13 '09 #88
bonneylake
769 512MB
Hey Acoder,

I know the neq 0 part is failing,because the isnumeric was working before. But cost is a float field in the table, not sure if its a string or not. Just know its float.

But i took out the cost neq 0 to make sure it was causing it and turns out its me using #pricediff# to output it is causing the problem. Its saying n/a for all of them based on i changed #cost# to pricediff#.Any suggestions?

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfif IsNumeric(cost) and cost neq 0.0>
  3. <cfset pricediff = ((#lowest# - cost)/cost)>
  4. <cfif pricediff gt 0.03 and pricediff lt -0.03>
  5. #pricediff#
  6. <cfelse>
  7. N/A
  8. </cfif>
  9. </cfif>
  10.  
Thank you,
Rach
Feb 13 '09 #89
acoder
16,027 Expert Mod 8TB
Add val() around cost. If it's displaying N/A, that means the price difference is less than +-3%.
Feb 13 '09 #90
bonneylake
769 512MB
Hey Acoder,

Around which cost? the one in isnumeric, the one where cost neq 0.0, the cost's in the pricediff? an then should i still be outputting using #pricediff#?

Thank you,
Rach
Feb 13 '09 #91
bonneylake
769 512MB
Hey Acoder,

I tried the following, but it still says all of the costs are n/a an that don't make sinse based on the math, especially for record 292 because when i do that one i get a value of 0.04.

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfif IsNumeric(cost) and #val(cost)# neq 0.0>
  3. <cfset pricediff = ((#lowest# - cost)/cost)>
  4. <cfif pricediff gt 0.03 and pricediff lt -0.03>
  5. #pricediff#
  6. <cfelse>
  7. N/A
  8. </cfif>
  9. </cfif>
I also tried the following (took out "and pricediff lt 0.03") an that shows all the prices above 0.03, an puts N/A for anything under 0.031.so would this work or not work?

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfif IsNumeric(cost) and #val(cost)# neq 0.0>
  3. <cfset pricediff = ((#lowest# - cost)/cost)>
  4. <cfif pricediff gt 0.03  >
  5. #pricediff#
  6. <cfelse>
  7. N/A
  8. </cfif>
  9. </cfif>


Also, i noticed something. I thought i was suppose to get less than +- 3% but i relooked at it an i am suppose to get >(greater than) + - 3% so will what we have still work? or is what we have only working to get less than?

Thank you,
Rach
Feb 13 '09 #92
acoder
16,027 Expert Mod 8TB
It's a bit of a duh! moment. The line:
Expand|Select|Wrap|Line Numbers
  1. <cfif pricediff gt 0.03 and pricediff lt -0.03>
will always be false because they are mutually exclusive. It needs to be or:
Expand|Select|Wrap|Line Numbers
  1. <cfif pricediff gt 0.03 or pricediff lt -0.03>
Feb 13 '09 #93
bonneylake
769 512MB
Hey Acoder,

Ok so i changed it to an or and now if a number if its greater than 3% it will show 0.04. An anything below 0.03 it shows n/a is this correct?

there is a few parts i am confused on. One value has - 0.39 and the other is 0.03 (one negative one positive). If you could explain what that means for one to be negative and one to be positive it would be helpful because it has me a bit off

another part i am confused on is where does the + 3% part show up?would that just be 0.03?

An the last question i have is there anyway to make the number smaller? right now i get 0.0489140480591, is there anyway to shorten that to just 0.04 (don't think they need to see the other numbers).I would do dollarfomart around it but not sure if it should be a price of not, i am guessing it should since its a price difference but thought i would ask your judgment on that one.

Thank you :),
Rach
Feb 13 '09 #94
acoder
16,027 Expert Mod 8TB
To round the number, use Round() and multiply the number by 100 to get the percentage, so 0.03 would become 3.

And yes, the +3 would be 0.03, but if you want to show a plus sign, you can add a cfif > 0 check to show +.
Feb 13 '09 #95
bonneylake
769 512MB
Hey Acoder,

Do you think they would want to see just the rounded number or see 0.03? They say % price different so i am thinking the 0.03, but i don't know how i just display the 0.03.

An also wanted to make sure, the if one value is -0.04 then it means its lower then 3 percent right?

An the last question i am a bit confused on how to show the + would i just do <cfif
pricediff gt 0.03 > + >#pricediff#</cfif>. Little bit confused on it.

Thank you,
Rach
Feb 13 '09 #96
acoder
16,027 Expert Mod 8TB
I'd think as a percentage, so you'd use round to round it to two decimal places, e.g. 0.03245678 would become 3.25% by multiplying by a 100 and then rounding. -0.04 is less than -3%.

Since you want to show the +, you could separate the two price differences, e.g.
Expand|Select|Wrap|Line Numbers
  1. <cfif pricediff gt 0.03>+#pricediff#
  2. <cfelseif pricediff lt -0.03>#pricediff#
  3. <cfelse>N/A
  4. </cfif>
Feb 14 '09 #97
bonneylake
769 512MB
Hey Acoder,

So is this the correct way to do the round? because mine didn't display like 3.25% it instead just kept putting first number 0. This is how i did it.

Expand|Select|Wrap|Line Numbers
  1. #Round(pricediff * 100)#
Thank you,
Rach
Feb 14 '09 #98
acoder
16,027 Expert Mod 8TB
To round to 2 decimal places, you'd multiply by 100 and then divide by 100, but since you want the decimal (0.03) as an integer too, you'd multiply by another 100, so something like:
Expand|Select|Wrap|Line Numbers
  1. <cfset pricediff = round(pricediff * 10000)/100>
Feb 15 '09 #99
bonneylake
769 512MB
Hey Acoder,

My last question (i hope is my last) is instead of displaying the round as your example how would i display it as .039 instead of how it originally was which was 0.394784718011 ? would something like #decimalFormat(Pricediff)# work?

here is what i got in total code wise now.

Expand|Select|Wrap|Line Numbers
  1. <cfset cost = #acompare.cost#>
  2. <cfif IsNumeric(cost) and #val(cost)# neq 0.0>
  3. <cfset pricediff = ((#lowest# - cost)/cost)>
  4. <cfset pricediff = round(pricediff * 10000)/100>
  5. <cfif pricediff gt 0.03>
  6. +#Pricediff#
  7. <cfelseif pricediff lt -0.03>#pricediff#
  8. <cfelse>
  9. N/A
  10. </cfif>
  11. </cfif>
Thank you,
Rach
Feb 16 '09 #100

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Bill Sneddon | last post: by
6 posts views Thread by martin | last post: by
2 posts views Thread by John Lau | last post: by
23 posts views Thread by Russ Chinoy | last post: by
15 posts views Thread by Giff | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.