For Loop to create QVD from xlsx

[Anonymous]

Hello,

I have got a Script error for which I would really appreciate the support of the Qlik Community.

Since the new year I cannot load the following script :

--------------------------------------------------------------------------------------------------------------------------------------------

// CASE OF THE PREVIOUS YEARS

for Year=2012 to 2025

for MonthNum=1 to 12

Set cFILE_DATA = [lib://DATA/$(Year)/DATA - GOOD ISSUANCE - $(Year) - $(MonthNum).xlsx] ;

set cFILE_QVD  = [lib://QVD/$(Year)/DATA - GOOD ISSUANCE - $(Year) - $(MonthNum).qvd] ;

LET cFILE_DATA_SIZE = FileSize(cFILE_DATA);

LET cFILE_QVD_SIZE = FileSize(cFILE_QVD);

// CHECK IF DATA EXISTS

if isnull(cFILE_DATA_SIZE) then

// DO NOTHING

else

// CHECK IF QVD EXISTS

if isnull(cFILE_QVD_SIZE) then

//CREATE QVD FILE

[DATA]:

LOAD

// Dimensions

FROM $(cFILE_DATA)

(ooxml, embedded labels, table is [DATA]);

STORE [DATA] into $(cFILE_QVD) (qvd);

DROP TABLE DATA;

else

// QVD EXISTS ALREADY => DO NOTHING

end if

end if

next MonthNum;

next Year;

--------------------------------------------------------------------------------------------------------------------------------------------

The script error tells me the "Table 'DATA' not found"

It is been 2 weeks I am try to debug this issue.

Could you please help me ?

Many thanks in advance for your support.

Matthieu

[thomaslg_wq]

Hi,

Please provide the script log.

Regards,

[petter]

The point where it fails is most probably here:

......

FROM $(cFILE_DATA)

(ooxml, embedded labels, table is [DATA]);

It indicates that a sheet named DATA is not to be found in the Excel workbook you're reading from. When reading from Excel the sheet is referred to as a table.

The  way around this is to set the ErrorMode variable to 0 which tells Qlik Sense to ignore errors which will allow you to at least read all the workbooks that really has a sheet named DATA.

Set ErrorMode=0;

[DATA]:

LOAD

// Dimensions

FROM $(cFILE_DATA)

(ooxml, embedded labels, table is [DATA]);

STORE [DATA] into $(cFILE_QVD) (qvd);

DROP TABLE DATA;

Set ErrorMode=1; // set the error handling back to normal

[Anonymous]

Hello Thomas,

Thanks very much for your possible future support.

The complete script is supplied above.

The only information missing is the columns loaded.

The script log error is this one :

L'erreur suivante s'est produite:

Table 'DATA' not found

Emplacement de l'erreur:

STORE [DATA] into lib://QVD/2012/DATA - GOOD ISSUANCE - 2012 - 2.qvd (qvd)

The script basically goes through the first loop but stops at the second iteration.

Last year I had no issue with the same code.

Cheers,

Matthieu

[Anonymous]

Hello Petter,

Thanks for your answer.

Nevertheless, this "Set ErrorMode=0" and "Set ErrorMode=1" do not solve the issue.

I keep investigating and debug my code but I cannot understand the issue as this code worked perfectly fine in 2017.

Many thanks again for your support,

Matthieu

[thomaslg_wq]

Check your data model at second iteration then to see if the table DATA exists as expected

With a :

If monthnum =2 then

Exit script

Endif

[Anonymous]

Just to check, if you set the maximum year to 2017 the script works fine?

[Anonymous]

Hello Josefina,

I have changed the maximum year from 2025 to 2017.

It doesn't make a difference.

The script now stops at iteration 4 as the first 2 QVDs were already generated.

All this is really above my developing skills.

It has been now 2 weeks I tried to debug this code which doesn't seem so complicated...

Many thanks to all of you !

Matthieu

[Anonymous]

Hello Thomas,

Everything seems oK.

All required columns are in the excel file.

The excel file contains a 'DATA' Tab.

I excuted your code and I have no issue.

The first iteration generates a proper QVD file.

Matthieu

[Anonymous]

¿Can you provide us two dummy data excel files?

Thanks!