topic Re: Get excel sheet names as field value in QlikView

Get excel sheet names as field value

renjithpl — Wed, 16 May 2012 08:00:47 GMT

Hi,

I found a solution from one of the link, to load data from different excel sheets,

for each vSheet in 'Jan$', 'Feb$','Oct$'

MyTableName:

Load ........From (biff,embedded labels,table is $(vSheet));

next;

but is there any coding where i want to take Jan, Feb, Oct as one field name called MonthName.

so when i click on Jan only jan sheet data should populate, and so on..

thanks

Re: Get excel sheet names as field value

RSvebeck — Wed, 16 May 2012 08:09:46 GMT

You can apply triggers on sheets in Qlikview. And the trigger can make a selection on a field of you choice. So if you click a sheet, it can filter the data that is shown on that sheet.

Best Regards, Robert

Re: Get excel sheet names as field value

stevedark — Wed, 16 May 2012 08:15:41 GMT

You just need to add an extra line to the load script to put the variable out to a field:

MyTableName:

LOAD
mid('$(vSheet)', 1, 3) as [Month Name],

....

From (biff,embedded labels,table is $(vSheet));

If you want the months to sort correctly you will need to add a bit more code:

Month(Date#('01 ' & mid('$(vSheet)', 1, 3) & ' 2000', 'DD MMM YYYY')) as [Month]

This will convert the text to a full date, before converting it back to a month.

Hope that helps,

Steve