topic Re: Loading from Excel. Only first doc loaded. in QlikView

Loading from Excel. Only first doc loaded.

Thu, 16 Oct 2014 14:13:20 GMT

Hi,

I'm trying to get the below to work. Works fine when fetching data from one excel document only. But when more than one, it only loads the very first one (sorted by name in my source data folder). Seems weird since it looks like it's fetching all of the lines in the 30 docs when pushing reload. But then only shows data from the first one when the reload is done.

Any ideas?

INITIAL:

LOAD
[Amount of transaction] as Amount,

Timestamp#([Settlement timestamp],'YYYY-MM-DD hh:mm:ss.fffff') As Timestamp,

[Counterparty ID] as ID,

[Account number] as ACC,
Date(Floor(Timestamp#([Settlement timestamp],'YYYY-MM-DD hh:mm:ss.fffff'))) As Date;

LOAD
     [Amount of transaction],
     [Settlement timestamp],
     [Counterparty ID],
     [Account number]

FROM

(biff, embedded labels, header is 2 lines, table is @1)
WHERE [Counterparty ID] <> 'Counterparty ID' and len(trim([Account number]));

NoConcatenate

FINAL:
LOAD
Date,
Timestamp,
Amount,
ACC,
ID,
If(Date <> Previous(Date),Amount,Amount+Peek(Balance)) As Balance

Resident INITIAL
Order by Timestamp;
Drop table INITIAL;

Re: Loading from Excel. Only first doc loaded.

Anonymous — Thu, 16 Oct 2014 14:19:43 GMT

Hi!

You can try doing something like this on your LOAD:

For each ExcelFile in filelist ('E:\Data\*.xls')

     LOAD
          [Amount of transaction],
          [Settlement timestamp],
          [Counterparty ID],
          [Account number]

     FROM
     [$(ExcelFile)]
     (biff, embedded labels, header is 2 lines, table is @1)

NEXT ExcelFile;

Re: Loading from Excel. Only first doc loaded.

MK_QSL — Thu, 16 Oct 2014 14:21:53 GMT

The document you are talking about is more than one excel file or more than one worksheet in one excel file?

Please confirm and also provide sample file to work..

Re: Loading from Excel. Only first doc loaded.

MarcoWedel — Thu, 16 Oct 2014 14:43:35 GMT

I recently had the same issue.

Preceding LOAD and wildcards in source filenames don't seem to go together.

So you can

- Load in a loop

- rewrite your LOAD statement to delete the preceding load

- move the preceding load to a join load

hope this helps

regards

Marco

Re: Re: Loading from Excel. Only first doc loaded.

Fri, 17 Oct 2014 13:50:09 GMT

Hi,

It's multiple excel files.

Pls see attached sample QV-file and two sample excel files. When I'm trying to reload only the Feb one goes into the QV-file.

Thanks in advance,

Olle

Re: Re: Loading from Excel. Only first doc loaded.

Fri, 17 Oct 2014 13:52:17 GMT

Hi Gabriel,

tried it, but I did not manage to get it right

Pls see the sample file I've attached below.

Kind REgards,

Olle

Re: Re: Loading from Excel. Only first doc loaded.

Fri, 17 Oct 2014 13:56:03 GMT

Thanks for your answer Marco!

I'm pretty new to QV, just learned what a preceding load is so unfortunately im a ittle lost when it comes to joins, loops and deleting preceding loads

Pls see the sample file I've attached in my answer above, any ideas?

Thanks in advance,

Olle

Re: Loading from Excel. Only first doc loaded.

martinpohl — Fri, 17 Oct 2014 13:59:16 GMT

unfortunately QlikView can't handle load preload within multiple files.

Change your script to

Temp:

LOAD
     [Amount of transaction],
     [Settlement timestamp],
     [Counterparty ID],
     [Account number]

FROM

(biff, embedded labels, header is 2 lines, table is @1)
WHERE [Counterparty ID] <> 'Counterparty ID' and len(trim([Account number]));

INITIAL:

noconcatenate LOAD
[Amount of transaction] as Amount,

Timestamp#([Settlement timestamp],'YYYY-MM-DD hh:mm:ss.fffff') As Timestamp,

[Counterparty ID] as ID,

[Account number] as ACC,

Date(

Floor(

Timestamp#(

[Settlement timestamp],'YYYY-MM-DD hh:mm:ss.fffff'))) As Date

resident Temp

;

drop table Temp;

NoConcatenate

FINAL:
LOAD
Date,
Timestamp,
Amount,
ACC,
ID,
If(Date <> Previous(Date),Amount,Amount+Peek(Balance)) As Balance

Resident INITIAL
Order by Timestamp;
Drop table INITIAL;

Regards

Re: Re: Re: Loading from Excel. Only first doc loaded.

Anonymous — Fri, 17 Oct 2014 14:06:58 GMT

Hi!

I've made some changes on your QVW. Please, take a look on this.

Regards,

Gabriel

Re: Loading from Excel. Only first doc loaded.

Tue, 21 Oct 2014 12:23:09 GMT

Great, thanks a lot!

//O