topic Re: Load Multiple spreadsheets & Crosstables in QlikView

Load Multiple spreadsheets & Crosstables

cbaqir — Sat, 16 Nov 2024 00:21:05 GMT

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.

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

Re: Load Multiple spreadsheets & Crosstables

rubenmarin — Sat, 11 Jul 2020 10:59:43 GMT

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

Re: Load Multiple spreadsheets & Crosstables

Kushal_Chawda — Sat, 11 Jul 2020 12:13:39 GMT

"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

Re: Load Multiple spreadsheets & Crosstables

Anonymous — Sun, 12 Jul 2020 05:58:53 GMT

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.

Re: Load Multiple spreadsheets & Crosstables

Brett_Bleess — Thu, 23 Jul 2020 12:21:33 GMT

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