8 Replies Latest reply: Feb 22, 2017 11:06 AM by Mikhail Bespartochnyy RSS

    Loading Dynamic Field List

    Mikhail Bespartochnyy

      Hello everyone,

       

      I'm working with a database that has couple of fields added to it each year at specific date. I'd like to write a script that would be dynamic and would bring in new fields when they are added to the database. Additionally, I'd like to remodel the database when it's loaded into QV to avoid growing number of fields.

       

      Essentially, each year we get data for current year and forecast for the next 3 years. Each year therefore, sometime in mid-December, we get a forecast data for new year. I'd like to write a script that would be dynamic and would bring in new fields when they become available. The issue that I ran into is that there is no consistency in date (i.e. December 31st each year) when a new fields are added, but we do know exact dates when new fields will be added.

       

      Does anyone know if there's a way to load new fields based on specific date? Or...if there's a way to check available fields and bring in those that are available, that would work too. There's no requirement to make the field be considered for load based on a date, any way to make the script dynamic will work.

       

      Attached is the database format to give you a better idea and a QVW with static load script I wrote so far.

       

      Many thanks for any help in advance!

       

      Thanks,

       

      Mikhail Bespartochnyy

        • Re: Loading Dynamic Field List
          Sunny Talwar

          In its current form, doing a dynamic load might not be possible, but if you are okay with the  The Crosstable Load, then you can do a LOAD * to bring all the columns which would essentially bring all the columns available in your Excel sheet.

            • Re: Loading Dynamic Field List
              Mikhail Bespartochnyy

              That is definitely what I was looking for! Thank you, Sunny! I am constantly amazed how you are able to solve a problem that I worked on for 2 days in 9 minutes

               

              I've read through the article you mentioned and an article about generic load and wrote a statement to bring in all the data dynamically and then convert it back into original format using generic load and FOR NEXT loop. Attached is revised QVW.

               

              I'm struggling now to reformat the table to desired format (Deal_Date & 8 Fields - Bid and Offer for each of 4 years). Do you know if there's a way now to move all of the fields into 8 fields either by availability of data in the field or by specific date? In other words do you know if there's a way to check at which point data in a field ends and start loading data from next field when first set of Bid and Offer fields become null?

               

              Thanks,

               

              Mikhail B.

                • Re: Loading Dynamic Field List
                  Sunny Talwar

                  Mikhail -

                   

                  I am glad it got you started. I am not sure I understand your question. Would you be able to elaborate a little?

                  Do you know if there's a way now to move all of the fields into 8 fields either by availability of data in the field or by specific date? In other words do you know if there's a way to check at which point data in a field ends and start loading data from next field when first set of Bid and Offer fields become null?

                    • Re: Loading Dynamic Field List
                      Mikhail Bespartochnyy

                      Sure. Essentially, the actual database that I'm working with has more fields than the one I attached initially (it has over 100+ of Bid and Offer columns). There is, however, a consistency between them and that consistency is that for each set of dates, there are 8 columns with Bid and Offer values - one Bid and Offer per Season.

                       

                      Instead of having a table in data model with 100+ fields, I'd like to compact it into:

                      • (1) Deal_Date field
                      • (4) Bid fields
                      • (4) Offer fields

                      and rename them as Season_1_Bid, Season_1_Offer, Season_2_Bid ... Season_4_Offer. Essentially, shortening the width of table from 100+fields to only 9.

                       

                      Mikhail B.