topic Re: For Loop to create QVD from xlsx in App Development

For Loop to create QVD from xlsx

Anonymous — Fri, 12 Jan 2018 01:44:06 GMT

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

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

Re: For Loop to create QVD from xlsx

thomaslg_wq — Fri, 12 Jan 2018 15:54:06 GMT

Hi,

Please provide the script log.

Regards,

Re: For Loop to create QVD from xlsx

petter — Sat, 13 Jan 2018 11:35:47 GMT

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

Re: For Loop to create QVD from xlsx

Anonymous — Mon, 15 Jan 2018 01:25:45 GMT

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

Re: For Loop to create QVD from xlsx

Anonymous — Mon, 15 Jan 2018 01:49:27 GMT

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

Re: For Loop to create QVD from xlsx

thomaslg_wq — Mon, 15 Jan 2018 17:16:26 GMT

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

Re: For Loop to create QVD from xlsx

Anonymous — Mon, 15 Jan 2018 19:40:36 GMT

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

Re: For Loop to create QVD from xlsx

Anonymous — Tue, 16 Jan 2018 00:30:43 GMT

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

Re: For Loop to create QVD from xlsx

Anonymous — Tue, 16 Jan 2018 00:38:44 GMT

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

Re: For Loop to create QVD from xlsx

Anonymous — Tue, 16 Jan 2018 05:51:08 GMT

¿Can you provide us two dummy data excel files?

Thanks!

Re: For Loop to create QVD from xlsx

thomaslg_wq — Tue, 16 Jan 2018 06:16:35 GMT

I meant: stop the script at the second iteration before the "drop table" sentence.

Then you will be able to check if the Table DATA exists in your Qlik App Data model as expected.

The goal is to stop the code execution just before the error and understand why it does not find any DATA table

Re: For Loop to create QVD from xlsx

Anonymous — Tue, 16 Jan 2018 07:20:13 GMT

Hello Thomas,

OK, I think your idea is very good. Thank you very much.

I can see the problem now.

In fact I have just created a new App and connected the first QVD generated by the code.

It seems :

- the name of the table created in the QVD is wrong.

- the column loaded are not the correct ones (correspond to another database loaded before)

Here is the column I want to load in QVD :

[DATA]:

LOAD

[Material],

[Order],

[Cost Center]

[Storage Location],

[Movement Type],

[Qty in Un. of Entry],

[Unit of Entry],

[Amount in LC],

[Posting Date],

[Time of Entry],

[User name],

FROM $(cFILE_DATA)

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

Here is the data contained in the QVD :

[DATA - GOOD ISSUANCE - 2012 - 1]:

LOAD

[Order],

[WO Material Number],

[WO Material description],

[MRP controller],

[BOM number]

FROM [lib://2012/DATA - GOOD ISSUANCE - 2012 - 1.qvd]

(qvd);

The columns loaded are the ones of another database I loaded before.

The name of the table is the name of the file.

A priori, I need to drop the table of the database loaded before or change its name.

Can you please give me your feedback ?

Many thanks.

Matthieu

Re: For Loop to create QVD from xlsx

thomaslg_wq — Tue, 16 Jan 2018 08:12:06 GMT

2 things to discuss :

1 : if your qvd is wrong, why do you want to use it anyway ?

2 : When you load columns in table with exactly the same columns as another previously loaded table, the new table is automatically concatenated to the first one.

If you want to avoid that behaviour, you need to add the "noconcatenate" sentence before loading the second table, as :

NoConcatenate

DATA:

LOAD *, etc.

Regards,

Re: For Loop to create QVD from xlsx

Anonymous — Wed, 17 Jan 2018 01:30:33 GMT

The code now generates all QVD. The size of all QVD varies and the columns insides are OK.

I think all QVD generated are now good.

The only issue is the name of the Table inside.

Instead of being named :

[DATA],

it is automatically set to

'[DATA - GOOD ISSUANCE - 2012 - 1]'

'[DATA - GOOD ISSUANCE - 2012 - 2]'

I guess this code :

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

DROP TABLE DATA;

shall define the name of the table inside QVD as [DATA]

Here is the full code :

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

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

//noconcatenate

[DATA]:

LOAD

[Material],

[Order],

[Cost Center],

[Storage Location],

[Movement Type],

[Qty in Un. of Entry],

[Unit of Entry],

[Amount in LC],

[Posting Date],

[Time of Entry],

[User name]

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

next MonthNum;

next Year;

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

After the 'QVD MONTH' are generated, I concatenate the 12 Months into a QVD Year.

After the 'QVD YEAR" are generated, I concatenate them into 1 QVD which is loaded into the App.

Re: For Loop to create QVD from xlsx

Anonymous — Wed, 17 Jan 2018 01:34:26 GMT

Hello Josefina,

Sorry, I cannot find how to add the data to the thread.

Nevertheless, I do not think the issue comes from the data.

Many thanks

Matthieu

Re: For Loop to create QVD from xlsx

thomaslg_wq — Wed, 07 Feb 2018 09:01:05 GMT

Hi again,

Sorry I did not see the notification.

There is no table name into a QVD.

When you load the QVD, you can :

Load the qvd without explicitly naming the table, the name will defines itself with the name of the source.
Explicit the name of the table you are loading as
TableName: LOAD *, etc.

Please mark the answer as correct if you solved your issue.

Regards,

Thomas