8 Replies Latest reply on Jun 29, 2014 6:18 AM by firebase

    Search Question

    firebase

      Title

      Search Question

      Post

           i have a price database with 4 fields GTIN, DATE, PRICE, PRICERANK (values are 1-5)

           each day i generate a new record.

           how can i filter to show only records from today which lost PRICERANK value 1 compared to yesterday?

        • 1. Re: Search Question
          philmodjunk

               What does each record represent?

               What is the GTIN field? What does a value in this field identify?

          • 2. Re: Search Question
            firebase

                 gtin is a product identifier. in this table we have several products prices.

                 that means for product "123456789" i can see prices for each day.

                 so each record represent a price of one product for one day.

                 so i want to find is all products which lost the price rank of 1 of yesterday to lets say price rank 2 of today.

            • 3. Re: Search Question
              philmodjunk

                   I was hoping that was the case as we can then use the gtin field as a match field in a sorted self join relationship to access data for the same product from the previous day in a calculation that will flag all records where the previous rank was 1 but that the current rank is not.

                   The relationship:

                   YourTable::gtin = YourTable 2::gtin and
                   YourTable::date > YourTable 2::Date

                   Double click the relationship line between these two table occurrences and click the sort button on the YourTable 2 side to specify that the YourTable 2 records be sorted by Date in descending order. This will make yesterdays record for the same product the first related record.

                   Then this calculation:

                   YourTable 2::Rank = 1 And YourTable::Rank ≠ 1

                   will return 1 (True) only in cases where the rank changed from yesterday's 1 to a lesser rank today. While it's an unstored record and thus slow to sort and search on, performing a find for all records that have 1 in this calculation field will pull up all products that fell out of the top rank today.

              • 4. Re: Search Question
                firebase

                     Ah Great, i got that!

                     just one further question:

                     How do i fill in a calculation into Perform find?

                • 5. Re: Search Question
                  philmodjunk

                       In this case, you don't. You define a calculation field for this and enter a 1 as your find criteria in perform find.

                       In cases where you do need to use a calculation to produce find criteria, use the scripted methods shown in this thread: Scripted Find Examples

                  • 6. Re: Search Question
                    firebase

                         Ah, i got that too. I did not realise that there is a add button :) so i thought it only allows one funktion.

                    • 7. Re: Search Question
                      firebase

                           It worked!

                           ...that was a true nightmare.

                           and thanx alot for your detailed and very prescise description, i dont want to know how long i would have spend by myself!

                      • 8. Re: Search Question
                        firebase

                             thanx again!

                             i have another problem

                             lets say i have a price database with 4 fields GTIN, DATE, PRICE, PRICERANK, SHOP

                             where shop is an url. now i also want to blacklist several shop, so they dot apear in the first sorting order.

                             so when i do this serach for changes from yesterday to today, those entry done apear because they contain a shop value that i store in another list.