How to load column dynamically everymonth

[Lucky1]

Hi Team,

I have a field name with "To be included May month data update" with values Yes and No

I want to load only yes values. while loading data i used

Load*

from DB

where To be included May month data update='Yes' ;

but the problem is everymonth that column name will be change ex next month the column name will be  "To be included June month data update".How to upload data dynamically

[Lisa_Sun]

The best practice is to keep the column name static.  In this case, I would suggest you to have two columns.  One is the Months Name and the other can be "To be included with month data update".  This is much flexible and easy to be maintained.  

[snibrahim1993 · Accepted solution]

Hi @Lucky1, If the data source is Excel you can load by No field names which means columns will be A, B, and C.... and you can use an alias name for that.

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!

[Kushal_Chawda]

@Lucky1  what is Source of your data. Are you loading it from QVD ?

[Lucky1]

Hi kushal my source is excel

[Lucky1]

Thanks for your reply snibdahim.i cannot rename for every field as I have many fields

[Kushal_Chawda · Accepted solution]

@Lucky1  try below

TempData:
first 1 LOAD *
FROM YourExcelFile.xlsx;

for i=1 to NoOfFields('TempData')

Fields:
Load FieldName($(i),'TempData') as FieldName
AutoGenerate 1;

next

Required_field:
NoConcatenate
Load *
Resident Fields
where FieldName like '*To be included * month data update*';

drop Tables Fields,TempData;

let vRequiredFieldName = Peek('FieldName',0,'Required_field');

Drop Table Required_field;

Final:
LOAD *
FROM YourExcelFile.xlsx
where "$(vRequiredFieldName)" ='Y';

[snibrahim1993]

@Lucky1  I Iunderstand,

Then you try the other method using by creating the simple variable

1. Store the current month's name in a variable. You can use the MonthName() function to get the current month name.
   LET vCurrentMonth = MonthName(Today());
   
   
2. Construct the column name dynamically using the stored month name.
   LET vColumnName = 'To be included ' & vCurrentMonth & ' month data update';
   
   
3. Use the constructed column name in your load script.
   Load
   *
   From DB
   Where [$(vColumnName)] = 'Yes';
   

[Kushal_Chawda]

@snibrahim1993  This approach will not work in case where you are in latest month but still column name not updated for latest month in data.  

[snibrahim1993]

Yes, but it will work when they change the data on the 1st of every month.