topic Re: Help with loading in Excel file in QlikView

Help with loading in Excel file

hopkinsc — Tue, 22 Apr 2014 08:44:01 GMT

Hi All,

I need to load in the attached file but unsure how because of the format.

I need the following headings as fields

Application

Team Name

Personnel

Week 1

Week 2 etc

day of week.

All of these are in the spreadsheet but unsure on how to get them in.

Is this possible please?

Thanks

Re: Help with loading in Excel file

its_anandrjs — Tue, 22 Apr 2014 08:50:34 GMT

Use Cross table function for loading this excel file.

Follow this URLs

Loading Cross Tables

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

Re: Help with loading in Excel file

hopkinsc — Tue, 22 Apr 2014 09:10:51 GMT

Hi Anand,

I have tried crosstable load but can't seem to get what i want.

Re: Help with loading in Excel file

hopkinsc — Tue, 22 Apr 2014 10:02:49 GMT

Does anyone have any thoughts on this?

Re: Help with loading in Excel file

bertdijks — Tue, 22 Apr 2014 10:35:40 GMT

Hi,

Scripting is here very complicated, almost impossible.

You have several issues for example:

- name day of week is not unique within the week

- 2 headings rows

Most simple solution is to ask the person who is generating the excel to replace the headings for day of week and/or the week nummer with a date field. You can then use calender functions to generate the day of the week and week nummer in your qlikview applicaition.

I hope this helps,

Bert

Re: Help with loading in Excel file

Gysbert_Wassenaar — Tue, 22 Apr 2014 13:48:42 GMT

Not all that complicated in this case:

T1:
CrossTable(Key,Value,3)
LOAD * FROM
[comm115094.xlsx]
(ooxml, embedded labels, Header is 1 lines, table is [52 Week Schedule]);

T2:
LOAD
  Application
  ,[Team Name:]
  ,[Personnel:]
  ,Key
  ,Value
  ,ceil(FieldIndex('Key',Key)/5) as Week
  ,dual(pick(mod(FieldIndex('Key',Key)-1,5)+1,'mon','tue','wed','thu','fri'),mod(FieldIndex('Key',Key)-1,5)+1) as Day
Resident T1;

drop table T1;

See attached qvw

Re: Help with loading in Excel file

hopkinsc — Tue, 22 Apr 2014 14:57:01 GMT

Hi Gysbert,

Thanks for your suggestion.

after looking at your script i can see that you are working out the week number from a 5 day week??

the data attached to my original post is only a sample and there are actually people who do work on a weekend also so is there a way i can include all days and get the correct week numbers too?

If i load my 'Actual' excel sheet then i get 56 weeks as a number of people have data for weekend also.

Any more help would be great..

Re: Re: Help with loading in Excel file

Gysbert_Wassenaar — Tue, 22 Apr 2014 19:04:59 GMT

No problem, just replace the nulls with real values first.

NULLASVALUE *;
SET NullValue = 0;

T0:
LOAD * FROM
[comm115094.xlsx]
(ooxml, embedded labels, Header is 1 lines, table is [52 Week Schedule]);

T1:
CrossTable(Key,Value,3)
load * Resident T0;

drop table T0;

T2:
LOAD
  Application
  ,[Team Name:]
  ,[Personnel:]
  ,Key
  ,Value
  ,ceil(FieldIndex('Key',Key)/7) as Week
  ,dual(pick(mod(FieldIndex('Key',Key)-1,7)+1,'mon','tue','wed','thu','fri','sat','sun'),mod(FieldIndex('Key',Key)-1,7)+1) as Day
Resident T1;

drop table T1;

Re: Re: Help with loading in Excel file

hopkinsc — Tue, 22 Apr 2014 19:15:16 GMT

is it That easy ??

Thanks I will give it a go tomorrow when I get in the office