Forum Discussion

cole_lehmkuhler's avatar
cole_lehmkuhler
Helper II
10 years ago
Solved

Edit Query Filtering Problems

I have a large amount of data with around 800,000 rows on specific sales that were recorded. I have a column for original purchase date, quarter, year, sale price, and also division (the divisions in...
  • ankitpatira's avatar
    ankitpatira
    10 years ago
    cole_lehmkuhler you first need to change data type of date column to Date which will result in rows with N/A to be error out. Then use Replace Error function and get rid of those errors with null or other date.
ankitpatira's avatar
ankitpatira
Community Champion
10 years ago
cole_lehmkuhler you first need to change data type of date column to Date which will result in rows with N/A to be error out. Then use Replace Error function and get rid of those errors with null or other date.
  • cole_lehmkuhler's avatar
    cole_lehmkuhler
    Helper II
    10 years ago

    This won't work because it will error out all of the options then because I'm not searching by purchase date. I'm searching by quarter which isn't in a date format. An example of a value that is in this column would be "Q3 2013" , but I'm only getting the filter option for Q1 2013 and Q2 2013 and all four quarters in 2012. I need to be able to filter between all quarters 2013-present

  • cole_lehmkuhler's avatar
    cole_lehmkuhler
    Helper II
    10 years ago

    My mistake I misread your response the first time, and it didn't work. I just started working with it again and went back to see if anyone else had responded and reread your answer and realized you were correct. Thank You!

    • chaz2jerry's avatar
      chaz2jerry
      Advocate IV
      8 years ago

      I also had this error of invalid value "#N/A" and solved by applying Replace Error step before other operational query steps.  If I apply Replace Error at the end of the query list then it won't work because the error can occur during one of the earlier steps.