How to use calculated field names in load statement

[kamal_sanguri]

Hi Experts,

I am trying to write a load statement, which can pull data from a file based on some calculated fields.

The data is in excel file and the name of the columns are like 1/1/2015, 2/1/2015... 12/1/2015.. so basically 12 columns representing 12 months. I have hard coded script which runs like

LOAD

     [42005], //equivalent to 1/1/2015

     [42036], //equivalent to 2/1/2015

     [42064],

     [42095],

     [42125],

     [42156],

     [42186],

     [42217],

     [42248],

     [42278],

     [42309],

     [42339]

FROM <DATASOURCE>

Since this data is coming from Excel file the dates are represented as numbers. I want to replace these hard coded numbers In order to make it more dynamic in the sense when we would have data for 2016. I created a variable, which can convert the first month of the current year to number for ex: 1/1/2015 would become 42005..this is how I can pull data for that month however for remaining months I am facing problem..

Here is the snippet of what I am trying to do:

[kamal_sanguri]

Yes, this can be achieved by creating 12 variables but I do not want to create so many variables.. I just want to create variable for first month and want to calculate other months using that variable..

I used addmonths but it didn't work as well ( I might not used it correctly)

Thanks.

[sunny_talwar]

And you want to bring them as columns and not use CrossTable Load to convert it into rows? Because if you use CrossTable you will be able to fix this issue very easily.

[kamal_sanguri]

hmm... how can it be corrected using cross table..?

[kamal_sanguri]

Well, Yes.. I need it as a normal table and not crosstab.. still curious how it can be corrected with crosstab..?

[sunny_talwar]

Have a look here: Re: Handling dates and cross table

[sunny_talwar]

Another one: excel date format and Cross table

[#8]

You can dynamically generate the Load script for month to month. The Idea is the Variable holds all the fields & its transformed field names. Please check below:

LET vMonthNo = Floor(MonthStart(Today())) ;

LET vLoadScript = '[$(vMonthNo)] AS ['&Date(vMonthNo,'MM/DD/YYYY')&']';

For i = 1 to 11

LET vMonthNo = Floor(MonthStart(AddMonths(Today(),-1*i))) ;

LET vLoadScript = '$(vLoadScript) , [$(vMonthNo)] AS ['&Date(vMonthNo,'MM/DD/YYYY')&']';

Next i

LOAD

  $(vLoadScript)

From <DataSOurce>

;

[#9 · Accepted solution]

I am generation on Today date value and you can use your own date value & generate the script dynamically.

[kamal_sanguri]

Nice one..!!