Passing data from excel to variable in script

[zagzebski]

Seems like this should be simple but want to grab a single date from an excel screadsheet and use in a script. So I just need to read the date from table below and put it into vDate variable.

Reporting_Date_Table:

LOAD [Date Reporting]
FROM
QVD\Layer1\Date_Control_Panel.qvd(qvd);

Let vDate =   ??? ;

[sunny_talwar · Accepted solution]

Does Date Reporting include multiple rows of data or just one row? For one row, you can try this:

LET vDate = Peek('Date Reporting');

For multiple rows, would you like to store them in different variable or would you like to store them in a comma separated list?

[zagzebski]

Thanks!

So what would be the best way if there were multiple rows (but I added a "date type" field with it)? 

[sunny_talwar]

Would you be able to share an example of how the data might look like and how you would store it?

[zagzebski]

Will share in a bit but a quick question -- I can't get this variable to read as a date. I use the variable in the function below but can't get it to work:

LET vDate = Peek('Date Reporting');  -- I date format this and it shows as 10/31/2015 but yet is not working in                                                                       the function below...

Function:

(InYearToDate(Date, $(vYTD), 0)
or InYearToDate(Date, $(vYTD), -1)
  or InYearToDate(Date, $(vYTD), -2)
  or InYearToDate(Date, $(vYTD), -3)
  or InYearToDate(Date, $(vYTD), -4)
  , 'YTD','NOYTD') as YTD,

[sunny_talwar]

I don't see where you are using this in the function below? I see vYTD, but not vDate

[zagzebski]

Ooops sorry should actually be:

LET vYTD = Peek('Date Reporting'); 

Still can't fiugre out why it won't read that as a date.

[sunny_talwar]

Give this a shot:

Reporting_Date_Table:

LOAD Date([Date Reporting], 'MM/DD/YYYY') as [Date Reporting]
FROM
QVD\Layer1\Date_Control_Panel.qvd(qvd);

Let vYTD = Date(Peek('Date Reporting'));

Function:

LOAD If(InYearToDate(Date, $(vYTD), 0)  or InYearToDate(Date, $(vYTD), -1) or InYearToDate(Date, $(vYTD), -2) or InYearToDate(Date, $(vYTD), -3) or InYearToDate(Date, $(vYTD), -4), 'YTD', 'NOYTD') as YTD,

From Source;

[zagzebski]

I tried that too. Couldn't  get it to work.

I even tried to make it really simple like this and it still wouldn't work. Every date was a "NOYTD", when it should only be the ones >= 10/31/2015:

Let vYTD = Date('10/31/2015','MM/DD/YYYY') ;

Function:

LOAD If(InYearToDate(Date, $(vYTD), 0)  or InYearToDate(Date, $(vYTD), -1) or InYearToDate(Date, $(vYTD), -2) or InYearToDate(Date, $(vYTD), -3) or InYearToDate(Date, $(vYTD), -4), 'YTD', 'NOYTD') as YTD,

From Source;

[sunny_talwar]

Let vYTD = MakeDate(2015, 10, 31);

or

LET vYTD = Date#('10/31/2015', 'MM/DD/YYYY');