Load multiple excel files with multiple sheets script

cmano — Thu, 05 Jan 2023 16:47:31 GMT

Hello guys i have a question.

I have 2 different excel books (Belgium & Czech Republic.xlx)

Each xlx file have 3 sheets ( IGT,SPL & FRN).

For IGT sheet (columns are):

Date

I.G.T.

Basket

A/T

Material description

Process order

Inspection lot

Order/Item

Material

Quantity

Characteristic

Min

Max

Value

Customer code

Customer description

Country

For SPL sheet (columns are):

Date

Basket

No Coil

Material description

Process order

Inspection lot

Order/Item

Material

Quantity

Characteristic

Min

Max

Value

Customer code

Customer description

Country

For FRN sheet (columns are):

No Coil

Material description

Process order

Inspection lot

Order/Item

Material

Quantity

Characteristic

Min

Max

Value

Customer code

Customer description

Country

They told me that QlikView creates synthetic keys and i have notice problem with data calculation.

What is have to do to load the data correctly?

Also i have problem export from the date (year, month,day , etc.) the Date form is DD-MM-YYYY.

Thank you so much.

Re: Load multiple excel files with multiple sheets script

QFabian — Thu, 05 Jan 2023 20:29:03 GMT

Hi @cmano , if you have 3 tables, but with the same fields , but some of the field name are not equal, yo can rename the fioelds to get the same fields structure, and if there are fields not in the 3 source tables, yo can create that missing fields, for example :

Load

Date as Date,
I.G.T. as I_G_T,
Basket as Basket,

Min,
Max,

..... normalize rest of fields

FROM IGT;

Load

Date as Date,

'' as I_G_T,

Basket as Basket,

Min,
Max,
..... normalize rest of fields

FROM SPL;

Load

Date as Date,

'' as I_G_T,

Basket as Basket,

Min,
Max,
..... normalize rest of fields

FROM

FRN;

Please check the help about it :

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/concatenate.htm

Example 2 – Appending multiple sets of data to a target table using implicit concatenation

Implicit concatenation will take place between any two tables that are loaded with identically named fields

topic Re: Load multiple excel files with multiple sheets script in App Development

Load multiple excel files with multiple sheets script

Re: Load multiple excel files with multiple sheets script

Example 2 – Appending multiple sets of data to a target table using implicit concatenation