topic Re: Excel data load, multiple tabs in QlikView

Excel data load, multiple tabs

Thu, 30 Aug 2012 09:14:32 GMT

Hi All,

Wonder if you could help me please as I am struggling with an excel dataload, am sure this is basic stuff but can't figure it out!

When I run the three seperate loads individually they work perfectly well however when I run them together they fetch the correct row count but then hang and force me to terminate the application as it freezes every time?

In this example I have commented out the third load to simplify things but surely that should also work if uncommented as they all have a common field?

Note tab 1 is called Opening and tab 2 is called closing - thanks in advance.

SET

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD * FROM

(ooxml, embedded labels, table is Opening);

LOAD * FROM

(ooxml, embedded labels, table is Closing);

//LOAD [DWF Ref],

//     [Date Instructions Received],

//     [Amount of Insured Damages Recovered],

//     [Date Closed],

//     if([Amount of Insured Damages Recovered]>=0,1,0)

//

//FROM

//

//(ooxml, embedded labels, table is Closing)

Re: Excel data load, multiple tabs

sreenivas — Thu, 30 Aug 2012 09:17:22 GMT

Can you add the excel files data too

Re: Excel data load, multiple tabs

Jason_Michaelides — Thu, 30 Aug 2012 09:22:14 GMT

Try naming your tables by adding TableName: before the LOAD and give your If() statement an alias using AS RecoveredFlag

Hope this helps,

Jason

Re: Excel data load, multiple tabs

Thu, 30 Aug 2012 09:24:12 GMT

Can you add the excel files data too -

Sorry I don't understand your question?

Re: Excel data load, multiple tabs

sreenivas — Thu, 30 Aug 2012 09:25:59 GMT

Add the excel files which you are refering

Re: Excel data load, multiple tabs

Thu, 30 Aug 2012 09:26:33 GMT

Thanks Jason,

Apologies as I am new to this, what is the correct syntax for table name before the load?

Thanks

Re: Excel data load, multiple tabs

Thu, 30 Aug 2012 09:27:27 GMT

I can add the excel file in a single data load as specified but can't add the tabs in a multiple load

Re: Excel data load, multiple tabs

Thu, 30 Aug 2012 09:41:37 GMT

Jason,

I have changed the code to the following and it is still freezing?;

SET

TableOpening:
LOAD * FROM

(ooxml, embedded labels, table is Opening);

TableClosing:

LOAD * FROM

(ooxml, embedded labels, table is Closing);

//LOAD [DWF Ref],

//     [Date Instructions Received],

//     [Amount of Insured Damages Recovered],

//     [Date Closed],

//     if([Amount of Insured Damages Recovered]>=0,1,0) as 'No. of Cases Closed with Recovery'

//

//FROM

//

//(ooxml, embedded labels, table is Closing)

Re: Excel data load, multiple tabs

sreenivas — Thu, 30 Aug 2012 10:40:01 GMT

Goto-->Editscript and debug the code using step by step

I think it is having the same column names in both the tables it may cause the syn tables and break the operation.

In Tableclosing and TableOpening add all the column names instead of "*" and post that script

Re: Excel data load, multiple tabs

Thu, 30 Aug 2012 10:50:35 GMT

paste

qualify *;

before first load

if works fine send us printscreen of structure from tableview

Re: Excel data load, multiple tabs

Thu, 30 Aug 2012 10:54:32 GMT

Thank-u!!!

What does that do??

Re: Excel data load, multiple tabs

Thu, 30 Aug 2012 11:01:27 GMT

press F1 and read some help:P