topic Re: Passing data from excel to variable in script in QlikView

Passing data from excel to variable in script

zagzebski — Sun, 06 Dec 2015 21:23:51 GMT

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 = ??? ;

Re: Passing data from excel to variable in script

sunny_talwar — Sun, 06 Dec 2015 21:27:36 GMT

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?

Re: Passing data from excel to variable in script

zagzebski — Sun, 06 Dec 2015 21:41:25 GMT

Thanks!

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

Re: Passing data from excel to variable in script

sunny_talwar — Sun, 06 Dec 2015 21:49:40 GMT

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

Re: Passing data from excel to variable in script

zagzebski — Sun, 06 Dec 2015 22:23:36 GMT

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,

Re: Passing data from excel to variable in script

sunny_talwar — Sun, 06 Dec 2015 23:15:34 GMT

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

Re: Passing data from excel to variable in script

zagzebski — Mon, 07 Dec 2015 00:18:05 GMT

Ooops sorry should actually be:

LET vYTD = Peek('Date Reporting');

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

Re: Passing data from excel to variable in script

sunny_talwar — Mon, 07 Dec 2015 00:22:45 GMT

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;

Re: Passing data from excel to variable in script

zagzebski — Mon, 07 Dec 2015 00:40:30 GMT

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:

From Source;

Re: Passing data from excel to variable in script

sunny_talwar — Mon, 07 Dec 2015 00:43:06 GMT

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