Loading Dynamic Field List

[mbespartochnyy]

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

[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.

[mbespartochnyy]

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.

[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?

[mbespartochnyy]

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.

[sunny_talwar · Accepted solution]

I think you want this:

Moving_Fields:

CrossTable(Attribute, Value)

LOAD *

FROM

[Moving Fields 2.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

NextTable2:

LOAD *,

  'Season_'&AutoNumber(RowNo(), Date&PurgeChar(Attribute, '0123456789'))&'_' & PurgeChar(Attribute, '0123456789') as Flag

Resident Moving_Fields

Where not WildMatch(Attribute, 'Mid*')

Order By Date, Attribute;

DROP Table Moving_Fields;

FinalTable:

LOAD DISTINCT Date

Resident NextTable2;

FOR i = 1 to FieldValueCount('Flag')

  LET vField = FieldValue('Flag', $(i));

  Left Join (FinalTable)

  LOAD Date,

  Value as [$(vField)]

  Resident NextTable2

  Where Flag = '$(vField)';

NEXT

DROP Table NextTable2;

[mbespartochnyy]

Yes! This is exactly it! Thank you so much, Sunny!

Mikhail B.

[sunny_talwar]

Awesome

You can use Generic Load here also, but I usually don't like Generic Load because its a two step process to combine it back into a single table. I prefer using For loop with FieldValue function.

[mbespartochnyy]

I'm with you on that one. It took a while for me to wrap my head around Generic Load and how to put the table it took apart back into one piece. Also, if we'll be using a For Loop anyway, makes a complete sense to skip Generic Load all together in this case.