topic Re: Excel to QlikFriendly Format in QlikView

Excel to QlikFriendly Format

Wed, 28 Sep 2011 15:55:22 GMT

Hi Guys,

The above is a screen dump of an Excel log that we wish to load into Qlikview.

Each Column is a Staff member and depending on whats populated into the cell on a particular date determines whether they are on Holidays (H) or in work (Null)

When I load this into Qlikview , the fields are now the list of staff names. I wish to have a Staff Name Field and a date field , I believe the below format is a more prudent load. Is it possible to amend the data within the load , or do I need to fix the Spreadsheet prior ?

Any help or thoughts on this , would be greatly appreciated !!! It looks like a big job me thinks !

Thanks

Re: Excel to QlikFriendly Format

Wed, 28 Sep 2011 17:31:58 GMT

You can use the crostable load statement.

Attached is a basic example.

You could get fancy and test for nulls and convert that to a working day. That is not included here.

Re: Excel to QlikFriendly Format

Thu, 29 Sep 2011 07:29:59 GMT

Thanks WMS

Works perfectly, 1 question though the rest of the dashboard has the dates as week month year etc, when I put the same format within the cross table

Date(Date) as Date,

Month(Date(Date)) as Month,

Week(Date(Date)) as Week,

Year(Date(Date)) as Year,

Each date then has a month value as part of the workstatus field ,

Re: Excel to QlikFriendly Format

Thu, 29 Sep 2011 10:41:38 GMT

I would create another table with the month,week and year dimensions and link it to your attendance table via the date field.

There are several calendar creation examples on this forum.

You could also do this.

Calendar:

Load distinct

date,

Year(date) as year,

Month(date) as month,

Week(date) as week

Resident Attendancetablename order by date:

Re: Excel to QlikFriendly Format

Thu, 29 Sep 2011 11:20:38 GMT

Thanks again wms