Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Excel to QlikFriendly Format

QlikStaff.bmp

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

Qlikstaff2.bmp

1 Solution

Accepted Solutions
Not applicable

Re: Excel to QlikFriendly Format

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.

4 Replies
Not applicable

Re: Excel to QlikFriendly Format

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.

Not applicable

Re: Excel to QlikFriendly Format

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 ,

Not applicable

Re: Excel to QlikFriendly Format

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:

Not applicable

Re: Excel to QlikFriendly Format

Thanks again wms