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.
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?
Moving Fields 2.qvw 295.2 K
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?
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.
I think you want this:
[Moving Fields 2.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
'Season_'&AutoNumber(RowNo(), Date&PurgeChar(Attribute, '0123456789'))&'_' & PurgeChar(Attribute, '0123456789') as Flag
Where not WildMatch(Attribute, 'Mid*')
Order By Date, Attribute;
DROP Table Moving_Fields;
LOAD DISTINCT Date
FOR i = 1 to FieldValueCount('Flag')
LET vField = FieldValue('Flag', $(i));
Left Join (FinalTable)
Value as [$(vField)]
Where Flag = '$(vField)';
DROP Table NextTable2;
Moving Fields 2 (1).qvw 219.8 K