Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Load Multiple spreadsheets & Crosstables

This is a 2 part question.

When I only load the Acute Care spreadsheet, both tabs in the QVW work correctly. The Hours tab is based on a cross table. The SLA table is not.

I now have additional spreadsheets that I am trying to load using FROM [..\External_Data\Epic Capacity Worksheet*.xlsx].

The first issue is that the two (will eventually be 3) spreadsheets are not concatenating and I can't figure out why. If I load only the Willow spreadsheet, I get "Field not found error Field '1/1/2019' not found". I have ensured the Excel formats are dates and I have tried date#( [1/1/2019],'M/D/YYYY') as [1/1/2019],  and date#(num#(Month),'M/D/YYYY') as Month. For the life of me, I cannot figure out why.

The second issue is that even if I comment out EPIC_HOURS_CROSSTABLE and Final and JUST LOAD EPIC_CAPACITY wth FROM [..\External_Data\Epic Capacity Worksheet*.xlsx], the data is not merging together as evidenced by the Primary Module List Box not containing the Willow options. 

2020-07-09_15-25-41.png

Any help on either of these issues would be much appreciated. TIA!

Labels (4)
4 Replies
rubenmarin

Hi, from what I see dates are being laoded as numbers (in string format, like '43497.000000').

I think you can avoid having to name all columns using '*', like:

LOAD... Status as HOURS_STATUS,
*
FROM
[.\Epic Capacity Worksheet*.xlsx]

 

If final table try to use "date(num#(Subfield(Month,'.',1),'0'),'M/D/YYYY') as Month" to convert data to date format

Kushal_Chawda

"Field not found error Field '1/1/2019' not found".

This actually look like formatting issues with excel. If you try to load the data I could see that column names are not appearing as '1/1/2019' instead it shows something like 'Jan1' 'Feb' etc..

Try change the format of column as text

Anonymous
Not applicable

As i can see headers are not same in your excel sheets, Different date values are acting as column headers, convert dates as date column by crosstable() then you will have only one date column with date values. 

Brett_Bleess
Former Employee
Former Employee

If one or more of the posts got you the information you needed, we would appreciate it if you can close out the thread by using the Accept as Solution button on the one(s) that did help.  If you did something different, you can post that and mark it, and if you have further questions, please leave an update post for us.  The posters only get credit for the help if you mark the solution, and that also lets other Members know what worked too...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.