Excel to QlikFriendly Format

[#1]

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

[#2 · Accepted solution]

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.

[#3]

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 ,

[#4]

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:

[#5]

Thanks again wms