Help with loading in Excel file

[hopkinsc]

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

[its_anandrjs]

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

[hopkinsc]

Hi Anand,

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

[hopkinsc]

Does anyone have any thoughts on this?

[bertdijks]

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

[Gysbert_Wassenaar]

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

[hopkinsc]

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..

[Gysbert_Wassenaar · Accepted solution]

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;

[hopkinsc]

is it That  easy ??

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