I have two tables: Tenders and Companies. Each record in the tenders table is a tender (contract) between a governmental entity and a company. The tender table has a column "keyword" which is a way to group similar tenders.
I'll try to explain my problem with a demo base. Here are the two tables:
As you can see, the companies table has three columns for each keyword. It's of type 'count' (it is filtered only to count each keyword)
Now, to turn the table from wide data schema into a narrow one, I can just use the table optimizer script available in the marketplace.
The issue appears when we talk about my real data and real base. I have 81 Keywords and the tenders table is updated every week so a new keyword might be added anytime. In order to follow the same procedure I explained with the demo base above, I need to create 81 columns for each keyword, and since the table optimizer script doesn't take count field (it needs to be text field) so you need to change these count fields to text fields. Oh, even though you then will be able to make the new narrow table, you still need to run the script on a weekly basis because the tenders table is populated with new records every week.
All I want is a way to see how many tenders each company won in each keyword group.
Any ideas?
