Loading from Excel. Only first doc loaded.

[#1]

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;

[Anonymous]

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;

[MK_QSL]

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

[MarcoWedel]

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

[#5]

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

[#6]

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

[#7]

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

[martinpohl · Accepted solution]

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

[Anonymous]

Hi!

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

Regards,

Gabriel

[#10]

Great, thanks a lot!

//O