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]

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

[Anonymous]

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

[thomaslg_wq]

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,

[Anonymous]

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

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.

[Anonymous]

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

[thomaslg_wq]

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